Last updated September 30, 2012. Created by bjaspan on July 16, 2007.
Edited by andreathegiant, meustrus, cato, xjm. Log in to edit this page.

The following table shows all the legal combinations of the 'type' and 'size' fields of a column specification along with the underlying database data types used by each combination. As of Drupal 7, MySQL, PostgreSQL, and SQLite data types are supported in Drupal core. Drupal 6 core supports MySQL and PostgreSQL.

The maximum size or range of each underlying type is also shown. For integer types, the number of storage bytes is given; the maximum range depends on whether the field is signed or unsigned. For varchar, text, and blob types, the maximum size is given.

type size MySQL type & size/range PostgreSQL type & size/range SQLite type
serial tiny tinyint, 1 B serial, 4 B integer
serial small smallint, 2 B serial, 4 B integer
serial medium mediumint, 3 B serial, 4 B integer
serial big bigint, 8 B bigserial, 8 B integer
serial normal int, 4 B serial, 4 B integer
int tiny tinyint, 1 B smallint, 2 B integer
int small smallint, 2 B smallint, 2 B integer
int medium mediumint, 3 B int, 4 B integer
int big bigint, 8 B bigint, 8 B integer
int normal int, 4 B int, 4 B integer
float tiny float, 4 B real, 6 digits float
float small float, 4 B real, 6 digits float
float medium float, 4 B real, 6 digits float
float big double, 8 B double precision, 15 digits float
float normal float, 4 B real, 6 digits float
numeric normal numeric, 65 digits numeric, 1000 digits numeric
varchar normal varchar, 255 B (D6) or 64 KB (D7 and later)1 varchar, 1 GB varchar
char normal char, 255 B character, 1 GB (UNSUPPORTED)
text tiny tinytext, 256 B text, unlimited text
text small tinytext, 256 B text, unlimited text
text medium mediumtext, 16 MB text, unlimited text
text big longtext, 4 GB text, unlimited text
text normal text, 16 KB text, unlimited text
blob big longblob, 4 GB bytea, 4 GB blob
blob normal blob, 16 KB bytea, 4 GB blob
datetime2 normal2 datetime, years 1001 CE to 9999 CE2 timestamp, years 4713 BCE to 5874897 CE2 (UNSUPPORTED)2

1. MySQL 5.0.3 or later (see http://dev.mysql.com/doc/refman/5.0/en/char.html).
PostgreSQL 9.0 or later (see http://www.postgresql.org/docs/9.0/static/datatype.html).
SQLite 3.0 or later (see http://www.sqlite.org/datatype3.html).

2. Note that Datetime support was removed from D7 db api - use mysql_type or pgsql_type if you want this functionality

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

For numeric fields, you must specify both precision and scale to generate the field.

For example, to create the equivalent to the MySql statement

`price` DECIMAL(10, 2) NOT NULL DEFAULT 0,

use the following
  'price' => array('type' => 'numeric', 'size' => 'normal', 'not null' => TRUE, 'default' => 0, 'precision' => 10, 'scale' => 2)

Alan Davison
Back roads somewhere in South America

Per Barry, at http://groups.drupal.org/node/7558#comment-26055, to add a custom type not already defined by schema API, like geospacial fields, you define your schema like the following, providing the right 'real' field type for alternative databases:

<?php
$schemea
['table']['fields']['foo'] = array(
 
'type' => 'varchar',
 
'mysql_type' => 'MAGIC_GEO_TYPE',
 
'pgsql_type' => 'whatever',
);
?>

Exactly put. But be very weary of portability. Thoroughly document the custom type to allow the application portability with specific database types of the next database type, (MySQL, MSSQL, PostGres, mSQL etc).

David K Hill
Director of Information Technology
Highlands Ranch Historical Society
Chief Information Officer
Greater Sacramento Area Masonic Service Bureau

Now that mysql supports geospatial column types, is there a module already started to add the geospatial column type to the drupal schema? I do not want to duplicate someones work :) Thanks.

Hello,

I have a table in an external database with a DATE field type. Seeing your code I was wondering where to put it in order to alter the schema, so when I will use views, the field will be recognized as a date, not as a string.

I have tested with hook_schema_alter, db_change_field($table, $field, $field_new, $spec) and I have also changed the views handler with data_table and nothing seems to work.

Any clue would be higly appreciated!

This one caught me out for a good while – I’ve got my own data stored in a database using Drupal’s Schema API and one field I want is a date, so I used the ‘datetime’ type. But whenever I came to use a value anywhere I couldn’t get any of Drupal’s date formatting functions to work – they were expecting a unix timestamp (makes sense) but the Schema API uses ‘datetime’ as a field type on MySQL so was getting a MySQL date string in return.

The answer was not to use datetime as a schema type, but int, and when passing data make sure you pass a timestamp. format_data() and views_handler_field_date etc. will then work as expected.

But it makes you wonder who left this mantrap lying around for foolhardy developers like me to fall into?

source: http://www.clusterdigital.co.uk/blog/09/09/storing-dates-drupal-schema-api

I also ran into this problem. Get a lot of notices on the "type" key.

I think it's really crappy to store dates as timestamp. However if this solves my situation, I'm happy.

You could use the MySQL datetime column type (which is preferable as it's easier to debug in the database and code) and just call the PHP function strtotime() on the resulting DB -formatted string to get a Unix-style timestamp, and it should parse out just fine...

----------
"Nothing is impossible - it just hasn't been done yet."
Phase2 Technology

Drupal "text / normal" become "text" type in MySQL. According to the documentation "A TEXT column with a maximum length of 65,535 (216 – 1) characters." ( http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html ) this is not 16 KB, but 64 KB.
I think this is a typo here.

Just a side note in case anyone wonders what the difference is - serial and int are the same internal column types, but serial is also set to be auto-increment, whereas int is not.

In other words, use serial for things like row IDs and int for columns that reference those IDs (or nid, vid, lid, etc) in other tables.

----------
"Nothing is impossible - it just hasn't been done yet."
Phase2 Technology

I am not sure why you decided to introduce this mapping. Would it not be easier for the schema to use the exact types in MySQL or Postgresql without this translation in which things get lost?
Simple solution:

     schema['mytable', 'mysql'] => array(.....);
     schema['mytable', 'postgres'] => array(....);
     //or to take things further
     schema['mytable', 'mssql'] => array(....);
     schema['mytable', 'ora'] => array(....);

The way it's done now, it's missing a lot of types that are built-in to each db engine. And I'm not talking about some weird strange types. I am talking about simple, elementary things like bit (bool, boolean) or date or time.

This is a huge limitation and I would hope to see it solved very soon although I know that's not possible.

I started with nothing and still got most of it.

From what I am seeing in the reasoning behind the use of "Drupal" data types rather than "SQL" data types appears to be concept that will enable Drupal to remain Database Independent. If we define the data types to correspond and behave like the native database, then Drupal becomes Database Concentric and quickly loses it's portability.
In other words devising an application that is locked to the basic underpinnings of the database brand prevents us from creating applications that are easily used on different database platforms.

In my humble opinion.

David K Hill
Director of Information Technology
Highlands Ranch Historical Society
Chief Information Officer
Greater Sacramento Area Masonic Service Bureau

Please note that according to http://drupal.org/node/866340, the datetime type has been deleted for Drupal 7. I pulled my hair out trying to get my module.install file to work until I found this. Unfortunately, I could not find a database-independent way to define the field, so I replaced these two lines:

'type' => 'datetime',  //The Drupal 6 way
'size' => 'normal',

with these two lines:
'mysql_type' => 'datetime',      // The equivalent MySQL data type
'pgsql_type' => 'timestamp',    // The equivalent PostgreSQL data type

I was having the same problem and your post helped me to solve it.

Was pulling my hair out why datetime wasn't working in 7. Peculiar....

yikes that should be updated in the docs..

Also spent too much time deciphering this. Could this be non-intuitive exception be highlighted in the documentation, please...


Tomáš J. Fülöpp @vacilandois

Thanks.

From the date module

      $db_columns['value'] = array(
        'type' => 'datetime',
        'mysql_type' => 'DATETIME',
        'pgsql_type' => 'timestamp without time zone',
        'sqlite_type' => 'VARCHAR',
        'sqlsrv_type' => 'smalldatetime',
        'not null' => FALSE,
        'sortable' => TRUE,
        'views' => TRUE,
      );

This assumes storage in UTC


Alan Davison
Back roads somewhere in South America

For a straight date, I use the following. Have not researched the equivalents outside of MySQL.

'date' => array(
    'type' => 'date',
    'mysql_type' => 'DATE',
    'pgsql_type' => 'timestamp without time zone',
    'sqlite_type' => 'VARCHAR',
    'sqlsrv_type' => 'smalldatetime',
),

Ohwow, if THAT really is what "DB independent" is supposed to mean... my good lord.

Is that serious? Wouldn't a true DB abstraction layer care for the necessary workarounds if a global datatype is not available in a certain DB backend? If I really do have to continue caring whether some DB API syntax works in a backend or not, then there is still a long way to go and the denotation "abstraction" is useless. Feels a bit like OOP where you are forced to edit private vars (if it were possible). Or where you have to query an object for its internals to make sure you may call a certain method.

Or am I getting something completely wrong here? Also, if not: Where is the best place to hook in and help this getting solved in favor of a *real* DB abstraction layer?

Just ranting because I was really happy thinking we'd already passed these hindering 'if ($db_type == 1) {} elseif (){}' stuff. But sounds like we didn't :'(

Edit: OK, for datetime, it is not considered a globally available type. My fault. But what about char? It would have to be removed as well - or is that just a documentation lack?

The abstraction is not *that* horrible. For example, you have a generic type "date" so that CRUD operations can be translated from code into SQL. You have some database / vendor specific mappings when defining the schema, and this is all declarative so there is no "if type" nonsense you suppose. You do it once, then... you use it in your code, and (in theory) in just works.

Honestly this pales in comparison to other issues in database design. Try supporting a truly I18N website with users across different timezones. Lots of fun.

Let's also keep in mind, this is not a fully blown ORM. (one can dream).

I did not say that vendor specific setups should be punished, there will always be some specific syntax someone will need for some special task. But abstraction is all or nothing. Either I do have a 100% reliable standard API so that I need no longer worry about databases or I can drop abstraction, I don't see any space for relativation there. If I explicitely want to design a module for e.g. a proprietary MSQL task, I can still do that but then I will use a specified db_query anyway, and not an API function like db_select etc.

The basic idea of the abstraction is actually really not to think about switches and fallbacks, that's literally expressed somewhere[tm] in the docs, as is the idea that I can implement API calls today which will even work if someone someday contributes a startrek_db or whatever_db interface. At least "char" is clearly breaking that up, and that even without being inevitable btw.

Thanks this solved my problem!

Thanks

length/size is mandatory when declaring a text field.

For length/size, use these options -> tiny, small, medium, normal, big.

Just adding this in here for an extra note for anyone that needs it. Thanks.