8

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"?

0

3 Answers 3

14

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.

2
  • 1
    Where might one find this ANSI standard?
    – Benitok
    Sep 28, 2018 at 14:40
  • 1
    @Benitok: see here and here
    – user1822
    Sep 28, 2018 at 15:18
1

There are some universal data types compatible across database systems as you can see here:

Universal data types

https://nils85.github.io/sql-compat-table/datatype.html

For example this SQL query works on all database systems:

CREATE TABLE test (
col1 INT PRIMARY KEY,
col2 SMALLINT,
col3 VARCHAR(126) NOT NULL,
col4 CHAR(255),
col5 DECIMAL(9,2),
col6 FLOAT(24),
col7 DOUBLE PRECISION)
0
0

Oracle tends to be the odd one out, with "NUMBER" covering bit, short, int, long, decimal, and float. It also uses CLOB and NCLOB for large text, and BLOB in place of IMAGE as used in SQL Server.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.