CODEWITHSUNDEEP
postgresqloracle-databaseansi-sql
David542
David542

Reputation: 110267

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

Upvotes: 0

Views: 3176

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246858

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

Upvotes: 5

Related Questions