OpenDBX/DBMS Datatypes

From Linuxnetworks
Jump to: navigation, search


Supported SQL data types[edit]

The tables below lists the SQL 2003 data types supported by the OpenDBX database backend implementations preceded by explanations of the types. Each cell contains values for language and type support, e.g. MySQL supports the BOOLEAN keyword but maps it to a 8 bit integer type whereas PostgreSQL supports both, the keyword and the type.

Integer types[edit]

  • BOOLEAN: 1 bit type from 0 to 1
  • SMALLINT: 16 bit type from -32768 to 32767
  • INTEGER: 32 bit type from -2147483648 to 2147483647
  • BIGINT: 64 bit type from -9223372036854775808 to 9223372036854775807
Database BOOLEAN SMALLINT INTEGER BIGINT
Firebird no / no yes / yes yes / yes yes / yes
MySQL yes [1] / no [2] yes / yes yes / yes yes / yes
Oracle no / no yes / no [6] yes / no [6] no / no [6]
PostgreSQL yes / yes yes / yes yes / yes yes / yes
SQL Server no / no yes / yes yes / yes yes / yes
SQLite yes / no [3] yes / no [3] yes / no [3] yes / no [3]
SQLite3 yes / no [4] yes / no [4] yes / no [4] yes / yes
Sybase ASE no / no yes / yes yes / yes yes / no [5]

[1] Supported since MySQL 4.1.0, before only BOOL was recognized

[2] MySQL maps BOOLEAN to TINYINT(1)

[3] SQLite stores integer types as strings in CLOBs

[4] SQLite3 always use BIGINTs for storing integer types

[5] Sybase use its NUMERIC type to support BIGINT values

[6] Oracle maps all integer types to its NUMBER type

Real BOOLEANs are only supported by PostgreSQL while MySQL converts BOOLEAN to tinyint, SQLite to CLOB type and SQLite3 to BIGINT type. For compatibility use CHAR(1) or SMALLINT instead - whatever fits best for your needs.

Decimal and floating point types[edit]

  • DECIMAL: Exact number with arbitrary precision and scale, e.g. DECIMAL(5,2) supports -999.99 to 999.99
  • REAL: 32 bit approximate type from -3.40e+38 to -1.17e-38 and from 1.17e-38 to 3.40e+38
  • FLOAT(p): Up to 64 bit approximate type with user defined precision (mantissa) from 1 to 53
  • DOUBLE PRECISION: 64 bit approximate type from -1.79e+308 to -2.22e-308 and from 2.22e-308 to 1.79e+308
Database DECIMAL(p,s) REAL FLOAT(p) DOUBLE PRECISION
Firebird yes / yes yes / yes yes / no [1] yes / yes
MySQL yes [2] / yes yes / yes yes / yes [2] yes / yes
Oracle yes / yes yes / no [5] yes / no [5] yes / no [5]
PostgreSQL yes / yes yes / yes yes / yes yes / yes
SQL Server yes / yes yes / yes yes / yes yes / yes
SQLite yes / no [3] yes / no [3] yes / no [3] yes / no [3]
SQLite3 yes / no [4] yes / no [1] yes / no [1] yes / yes
Sybase ASE yes / yes yes / yes yes / yes yes / yes

[1] Implementations always use a double value

[2] Supported since MySQL 3.23

[3] SQLite stores all values as CLOBs

[4] SQLite3 returns for DECIMAL the same type as for DOUBLE PRECISION

[5] Oracle maps all floating point types to its NUMBER type

Character types[edit]

  • CHARACTER: Fixed length character data type
  • NATIONAL CHARACTER: Fixed length national character data type
  • CHARACTER VARYING: Variable length character data type
  • NATIONAL CHARACTER VARYING: Variable length national character data type

The maximum length of these types is at least 255 bytes which results in 255 or less characters because national characters can use more than one byte. You should consider the CLOB type if you need a character type with more space.

Database CHARACTER NATIONAL CHARACTER CHARACTER VARYING NATIONAL CHARACTER VARYING
Firebird yes / yes yes / no [1] yes / yes yes / no [1]
MySQL yes / yes yes [2] / no [3] yes / yes yes [2] / no [3]
Oracle yes / yes yes / no [1] yes / yes yes / no [1]
PostgreSQL yes / yes yes / no [1] yes / yes yes / no [1]
SQL Server yes / yes yes / no [1] yes / no [1] yes / no [1]
SQLite yes / no [4] yes / no [4] yes / no [4] yes / no [4]
SQLite3 yes / no [4] yes / no [4] yes / no [4] yes / no [4]
Sybase ASE yes / yes yes / no [1] yes / no [1] yes / no [1]

[1] Implementations map NATIONAL CHARACTER (VARYING) to CHARACTER (VARYING)

[2] Supported since MySQL 4.1.0 with UTF-8 character set as default

[3] MySQL maps NATIONAL CHARACTER (VARYING) to CHARACTER VARYING with UTF-8 character set

[4] SQLite and SQLite3 map all varieties of CHARACTER types to CLOBs using the UTF-8 character set

Many database implementation support abbreviations like CHAR or VARCHAR but you should use the SQL2003 names for maximum compatibility. For the same reason, using a larger maximum of (NATIONAL) CHARACTER VARYING as supported by many databases isn't recommended.

Large object types[edit]

  • CLOB: Large variable length character data type with a limit of ca. 32 KB
  • NCLOB: Large variable length national character data type
  • XML: Variable length character data type for XML data
  • BLOB: Variable length binary data type
Database CLOB NCLOB XML BLOB
Firebird no / no no / no no / no yes / no
MySQL no [1] / yes no / no no / no yes / yes
Oracle yes / no [5] yes / no [5] no [4] / no yes / no
PostgreSQL no [1] / yes no / no no / no no / no
SQL Server no [1] / yes no [6] / no yes / no no [3] / yes
SQLite yes / yes no [2] / no no / no no / no
SQLite3 yes / yes no [2] / no no / no yes / yes
Sybase ASE no [1] / yes no / no no / no no [3] / yes

[1] Implementations use TEXT instead of CLOB

[2] Implementations support the long form NATIONAL CHARACTER LARGE OBJECT

[3] MS SQL Server and Sybase ASE use IMAGE instead of BLOB

[4] Oracle use XMLTYPE instead of XML

[5] Oracle CLOBs converted to strings have a limit of 4000 bytes instead of 32 KB

[6] MS SQL Server uses NTEXT instead of NCLOB

Temporal types[edit]

  • TIME: Time data type in HH:mm:ss format
  • TIME WITH TIME ZONE: Time data type in HH:mm:ss+hh or HH:mm:ss-hh format
  • TIMESTAMP: Combined date and time data type in YYYY-MM-DD HH:mm:ss format
  • TIMESTAMP WITH TIME ZONE: Combined date and time data type in YYYY-MM-DD HH:mm:ss+hh or YYYY-MM-DD HH:mm:ss-hh format
  • DATE: Date data type in YYYY-MM-DD format
  • INTERVAL: Time or date interval
Database TIME TIME WITH TIME ZONE TIME STAMP TIME STAMP WITH TIME ZONE DATE INTERVAL
Firebird yes / yes no / no yes / yes no / no yes / yes no / no
MySQL yes / yes no / no no [1] / yes no / no yes / yes no / no
Oracle no / no no / no yes / yes yes / yes yes / yes yes / yes
PostgreSQL yes / yes yes / yes yes / yes [2] yes / yes yes / yes yes / yes
SQL Server no / no [4] no / no no [1] / yes no / no no / no [4] no / no
SQLite yes / no [3] yes / no [3] yes / no [3] yes / no [3] yes / no [3] no / no
SQLite3 yes / no [3] yes / no [3] yes / no [3] yes / no [3] yes / no [3] no / no
Sybase ASE yes / no [5] no / no no [1] / yes no / no yes / no [5] no / no

[1] Implementations use DATETIME instead of TIMESTAMP as defined by the SQL standard

[2] PostgreSQL 7.3 and later is compliant to TIMESTAMP as defined by the SQL standard

[3] SQLite and SQLite3 store temporal values as CLOBs

[4] MS SQL Server doesn't support the TIME and DATE data type. Instead, only DATETIME is supported which is the same as the ANSI TIMESTAMP data type

[5] Sybase ASE convert TIME and DATE values to its DATETIME type and therefore they are always printed in YYYY-MM-DD HH:mm:ss format

Misc types[edit]

  • ARRAY: Variable length set of the same data type
  • MULTISET: Set of different data types
  • DATALINK: Link to external (file) resource
Database ARRAY MULTISET DATALINK
Firebird no / no [1] no / no no / no
MySQL no / no no / no no / no
Oracle no / no no / no no / no
PostgreSQL yes / yes no / no no / no
SQL Server no / no no / no no / no
SQLite no / no no / no no / no
SQLite3 no / no no / no no / no
Sybase ASE no / no no / no no / no

[1] Firebird / Interbase only support a non-standard implementation of the ARRAY type

These types are so rarely implemented that you should not even consider using them if you want to write portable code.