Databases: are there universal datatypes? While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in the future?  While switching, I would like to avoid changing datatypes of the columns that are NOT available in the new database to appropriate datatypes that are available in the new database.

In other words, are there any ANSI datatypes that will work in all the databases with the same size?  That is, similar to "ANSI SQL", are there anything like "ANSI SQL Data types"?
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,
   product_name  varchar(100),
   price         decimal(10,2)
);


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 varchar to varchar2).

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
CHARACTER VARYING (or VARCHAR)
CHARACTER LARGE OBJECT 
NCHAR
NCHAR VARYING
BINARY
BINARY VARYING
BINARY LARGE OBJECT
NUMERIC
DECIMAL
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE PRECISION
BOOLEAN
DATE
TIME
TIMESTAMP
INTERVAL


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


VARCHAR
INTEGER
DECIMAL
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.