Last updated September 30, 2012. Created 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
PostgreSQL 9.0 or later (see
SQLite 3.0 or later (see

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 forums, or join #drupal-support in IRC.


Alan D.’s picture

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)
KarenS’s picture

Per Barry, at, 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:

['table']['fields']['foo'] = array(
'type' => 'varchar',
'mysql_type' => 'MAGIC_GEO_TYPE',
'pgsql_type' => 'whatever',
dkhill’s picture

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

jjchinquist’s picture

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.

Publius’s picture


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!

sobi3ch’s picture

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?


ricbra’s picture

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.

arpieb’s picture

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

foripepe’s picture

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." ( ) this is not 16 KB, but 64 KB.
I think this is a typo here.

arpieb’s picture

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

alphadog’s picture

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.

dkhill’s picture

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

pilotget’s picture

Please note that according to, 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
LSU_JBob’s picture

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

samhassell’s picture

yikes that should be updated in the docs..

Vacilando’s picture

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

ajinkya.kulkarni’s picture


Alan D.’s picture

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

peterx’s picture

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',
doitDave’s picture

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?

aaronaverill’s picture

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).

doitDave’s picture

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.

rayjames’s picture

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.

goldenflower’s picture

i am writing the below code

'total' => array('type' => 'numeric','precision' => 10,'scale' => 2,'size' => 'normal'),

even not working with

'total' => array('type' => 'numeric','precision' => 10,'scale' => 2),

but getting out of range error

ORA-01727: numeric precision specifier is out of range

Best Regards,
Ghanshyam Singh

phponwebsites’s picture

I want to add Phone number. So i added new integer field at content type to store phone number. When i create a content with phone number, it gives error:

PDOException: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'field_commerce_mobile_number_value' at row 1: INSERT INTO {field_data_field_commerce_mobile_number} (entity_type, entity_id, revision_id, bundle, delta, language, field_commerce_mobile_number_value) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array ( [:db_insert_placeholder_0] => commerce_customer_profile [:db_insert_placeholder_1] => 324 [:db_insert_placeholder_2] => 324 [:db_insert_placeholder_3] => billing [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => 9499999999 ) in field_sql_storage_field_storage_write() (line 494 of /home/guru/Public/rgit/drupal7/modules/field/modules/field_sql_storage/field_sql_storage.module).

When i looked in database, the length of field is 11. But i can't add 10 digit phone number. I can add '0499999999' but i can't can '9989589909' based on answer

According to, we can't store value greater than 2147483647 in integer datatype. We have to change datatype to store large value like phone number.

Alan D.’s picture

0XXXXX is saved as XXXXX, so this is the wrong type for starters, Simply use a varchar (text) field type.