Learning SQL - SQL Data Types

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Introduction

Lots of folks get confused with SQL database data types. I have known some database designers get almost violent in defence of the data type they have spent days analysing, then have sleepless nights worrying if they should have used a varchar, char, integer or whatever. Don't worry too much about it! When you are designing massive, mission critical databases you can spend your whole life fussing over data types - SQLite makes it really easy for you!

SQLite Data Types

There are only five! See, I told you it was easy.

INTEGER - A number without decimals, a whole number.

REAL - A 'floating point' number, a number with decimals

NUMERIC - A number with or without decimals

TEXT - Yes, you guessed it - Text!

BLOB - A Binary Long Object

In fact SQLite only actually stores numbers in two states - Integer or Real. You don't have to worry about the size of Integers - SQLite will use between 1-8 bytes or (8-64 bits if you're that way inclined), depending on the value inserted.

SQLite is very forgiving with data types. If you create a table with all numeric columns, it will allow you enter ANY of the data types in the columns. This is good as you can be very lazy with your column designing but bad as you will need to control values being entered into your database with code. Any SQLite column (except Integer Primary Key) can store any type of data!

Here's an example - Creating a SQLite table:

create table QiSQL_Column_Test (
today nosuchcolumn ,
tomorrow datetime ,
nextfriday text,
mybirthday numeric ,
myname numeric ,
mymiddlename integer ,
mylastname boolean );

insert into QiSQL_Column_Test (
today , tomorrow , nextfriday , mybirthday , myname , mymiddlename , mylastname )
values ( 1,2,3,4,5,6,7 );

insert into QiSQL_Column_Test (
today , tomorrow , nextfriday , mybirthday , myname , mymiddlename , mylastname )
values (
'monday' , 'tuesday' , 'wednesday' ,'thursday', 'friday' , 'saturday' , 'sunday' );

No problem! A column with datatype of 'nosuchcolumn' - OK! Inserting text into data fields, text into integer, boolean and numeric fields - no worries! You caneven invent your own data types ( SQLite will assign them as numeric ) - though I don't know why you would want to do that!

When we query some data back SQLite magically converts datatypes for us:

select tomorrow + today calculation , nextfriday from QiSQL_Column_Test;

We get these results:

calculation nextfriday
3 3
0 wednesday

In the first row of data SQLite is quite happy to add the values of the first two columns as the values we inserted are numeric. In the second row, adding 'monday' and 'tuesday' is not numeric so we get zero.

What does this mean? YOU have to control the values going into your database! However - you will see in a later lesson - Constraints, that with some jiggery pokery we can enforce strict rules on waht goes into our database!

But....

If you are using SQlite as a stepping stone onto a higher end Database like MySQL - use the datatypes that this database expects. You will be able to write migration applications much more easilly.

Other SQL Data Types

Other flavours of SQL databases use different data types and use 'Static Typing' to control values inserted into their database tables. SQLite as explained above use the value entered to determine the type which is called 'Manifest Typing'. You don;t need to worry about typing, I just thought it might be interesting.

If you are not interested in other SQL Databases skip the next few sections.

MySQL Data Types

Looking at MySQL there are so many numeric data types it's no wonder folks get confused!

Integer data types are TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINT - As well as BIT and BOOLEAN.

Floating Point data types are FLOAT , DOUBLE, DECIMAL. The Floating Point and Integer types can also be specified as UNSIGNED and ZEROFILL.

With Text data types you get the CHAR and VARCHAR types. The difference between these two is that a CHAR is a fixed size. If you create a column with a CHAR(20) definition, the data in that column will always be right padded with spaces to 20 characters long. I would think that the only reason you would want to use a CHAR field is to speed up comparisons as the length of strings compared will always be the same. MySQL also has BINARY, VARBINARY types which hold binary strings. There are BLOBS like SQLite but they are split into TINYBLOB, BLOB, MEDIUM BLOB and LOBGBLOB. There also TEXT columns which as far as I can tell are the same as VARCHARS. Finally their is the SET type which I find very confusing!

The full details of MySQL Data Types can be found on the MySQL site ( MySQL Version 5 Reference Manual).

Link checked 6th May 2011

PostGreSQL Data Types

PostgreSQL like MySQL, has SMALLINT, INTEGER and BIGINT to store values from 2 to 8 bytes. Then there is DECIMAL and NUMERIC (the same thing) for fixed scale decimal numbers. REAL, and DOUBLE PRECISION deal with the floating point numbers. There two data types used to create Serial numbers - SERIAL and BIGSERIAL for huge serial numbers!

The Text data types in PostgreSQL are similar to the chare and varchar types in MYSQL. PostGreSQL uses CHARACTER, CHARACTER VARYING and TEXT (CHAR and VARCHAR definitions will do the job as aliases)

PostGreSQL Data Types can be found on the PostgreSQL 8.4 Documentation page.

Link checked 6th May 2011

Access Data Types

I'm not a fan of MS Access, but lots of folks are so here's a quick summary of the data types.

BINARY, VARBINARY, and LONGBINARY are used to store binary data

SINGLE, DOUBLE, SHORT, LONG, NUMERIC, UNSIGNED BYTE are used to hold numbers

CHAR, LONGVARCHAR, and VARCHAR for text values

More information may be found on the Microsoft Access Data Types page.

Link checked 6th May 2011

Oracle Data Types

Oracle uses a VARCHAR2 column for variable length text data - so it must be better as it's got a 2 on the end! Remember the amplifier in Spinal Tap that went up to 11? Sorry, I'm gettig a bit flippant, let's get back to the data types. CHAR2 is also available for text values padded to the specified length.

NUMBER is used for numbers and you can specify scale and precision.

Oracle also has fully expanded Data Type specifications for PL/SQL which according to Oracle is "an imperative 3GL that was designed specifically for the seamless processing of SQL commands." I guess you could call it a procedural programming language for Oracle databases. I used it for many years and loved it! In the bad old days before PL/SQL we had to use C for everything we couldn't do with SQL.

More information on Oracle Data Types may be found at the Oracle Native Datatypes page.

Link checked 6th May 2011