For the most basic datatypes, nearly all DBMS either support the ANSI data types directly or at least allow to specify them.
The following statement:
create table products
product_id integer not null primary key,
works on nearly every DBMS. The data types will however be translated to the DBMS-specific ones (e.g.
integer will be changed to
number in Oracle, or
When it comes to more "advanced" datatypes like CLOB, BLOB, XML, Json, or geospatial types then this doesn't hold true any more (CLOB, BLOB is pretty common, but e.g. not understood by Postgres although you can easily create a domain or a user data type with that name to make it more compatible).
The basic data types as defined by the ANSI standard are:
- CHARACTER VARYING (or VARCHAR)
- CHARACTER LARGE OBJECT
- NCHAR VARYING
- BINARY VARYING
- BINARY LARGE OBJECT
- DOUBLE PRECISION
Not all DBMS suppor all of them (MySQL doesn't have an interval data type, MySQL and SQL Server don't have a real boolean type, Oracle does not have a boolean type at all or a real DATE or TIME data type, Postgres doesn't have
nchar - that list could go on and on). But the most common ones usually work quite well.
From my experience, the following ones work without (major) problems across different DBMS (although that is my experience - this is by far not a complete or definite list!)
- DATE (with surprises: Oracle has it but includes a time)
- TIMESTAMP (does something different than expected on SQL Server and MySQL)
If you stick with those, you are pretty much "universal", but anything else will give you some (unpleasant) surprises when you go from one DBMS to another.