Community Documentation

Data types

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

Comments

For numeric fields, you must

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

Custom types

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',
);
?>

Custom types

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
Chief Information Officer
Greater Sacramento Area Masonic Service Bureau
Chief Financial Officer
Masonic Hall Association of Wheatland

Geospatial data column module?

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.

Problem with storing dates in Drupal Schema API

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.

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.

Try using strtotime()

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

text / normal in MySQL

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.

Serial vs int

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

Few useful datatypes missing

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.

Why not SQL Specific Datatypes - Simple Answer

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
Chief Information Officer
Greater Sacramento Area Masonic Service Bureau
Chief Financial Officer
Masonic Hall Association of Wheatland

Datetime is gone for Drupal 7

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

Thanks for posting

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

Thanks from me also

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

yikes that should be updated

yikes that should be updated in the docs..

Also spent too much time

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

------
Tomáš J. Fülöpp @vacilando.is

Thanks.

Thanks.

From the date module      

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

date

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

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?

abstraction != isolation

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

Specific ./. abstraction

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.

no Schema type for mysql type datetime

Thanks this solved my problem!

Thanks

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.

nobody click here