Search code examples
postgresqloracle-databaseansi-sql

Type system: ANSI SQL (standard) vs. Postgres, Oracle, DB2

This is a pretty general question, and am looking to find an expert who is both familiar with the standard as well as a few DB systems. At a high-level, what are some of the differences between the type system as described in the SQL standard vs that implemented in Postgres, Oracle, or DB2.

I'm just making something up (and it's incorrect -- and just used to show the format of the type of comparison I'm interested in seeing), but an example might be along the lines of:

Standard Postgres Oracle
INTERVAL type Does not exist, closest is TIME Same as standard.

(As an aside, would this question be better for DBA?)

Solution

  • Few people will be able to give you a whole complete list, so I'll just tell you what I know, without any claim to completeness. I also restrict myself to the database I know better.

    Comments to the database's implementation describe deviations from the standard.

    SQL standard PostgreSQL Oracle Comments
    CHARACTER ✔ empty strings are treated as NULL weird standard-dictated semantics; avoid
    CHARACTER VARYING ✔ empty strings are treated as NULL
    CHARACTER LARGE OBJECT non-standard name text or varchar ✔
    NATIONAL CHARACTER ✔ empty strings are treated as NULL obsolescent
    NATIONAL CHARACTER VARYING ✔ empty strings are treated as NULL obsolescent
    NATIONAL CHARACTER LARGE OBJECT non-standard name text or varchar empty strings are treated as NULL obsolescent
    BINARY LARGE OBJECT non-standard name bytea ✔
    NUMERIC ✔ ✔
    DECIMAL ✔ ✔ virtually the same as NUMERIC
    SMALLINT ✔ ✔ (same type as NUMERIC)
    INTEGER ✔ ✔ (same type as NUMERIC)
    BIGINT ✔ ✔ (same type as NUMERIC)
    FLOAT ✔ ✔ (same type as NUMERIC)
    REAL ✔ ✔ (same type as NUMERIC, but there is also BINARY_FLOAT)
    DOUBLE PRECISION ✔ (same type as NUMERIC, but there is also BINARY_DOUBLE)
    BOOLEAN ✔ ❌ (but allowed in PL/SQL)
    DATE ✔ is really a timestamp
    TIME ✔ ❌
    TIME WITH TIME ZONE ✔ ❌ semantics unclear; avoid
    TIMESTAMP ✔ ✔
    TIMESTAMP WITH TIME ZONE semantics differ from the standard ✔
    INTERVAL ✔ ✔
    ROW composity types object types
    REF ❌ ✔ (non-standard syntax)
    <type> ARRAY ✔ vararray types
    <type> MULTISET ❌ ❌
    XML ✔ non-standard name XMLTYPE