Cross DBMS Table features and nuiances

The following list out the most common TABLE features supported by the most popular DBMS as off 2024-07-15. This is still work in progress. You should trust but verify the information in this cheat sheet.

Features	ANSI_99	DB2_LUW	MS_SQL	Oracle	SQLite	MySQL	PostgreSQL
Create Table:
`IF NOT EXISTS`	Y	Y		Y	Y	Y	Y
Data Types:
Boolean Types:
`BOOLEAN`	Y	Y				Y	Y
`BIT`			Y
Integer Types:
`TINYINT`			Y			Y
`SMALLINT`	Y	Y	Y			Y	Y
`MEDIUMINT`						Y
`INTEGER` \ `INT`	Y	Y	Y		Y	Y	Y
`BIGINT`		Y	Y			Y	Y
`MONEY`			Y				Y
`SMALLMONEY`			Y
`SERIAL` \ `SMALLSERIAL` \ `BIGSERIAL`							Y
Fixed Point Types:
`DECIMAL`	Y	Y	Y			Y	Y
`NUMBER`				Y
`NUMERIC` \ `NUM`	Y	Y	Y		Y	Y	Y
Floating Point Types:
`FLOAT`	Y	Y	Y		Y	Y	Y
`REAL`	Y	Y	Y				Y
`DOUBLE`						Y
`DOUBLE PRECISION`	Y	Y				Y	Y
`BINARY_FLOAT` \ `BINARY_DOUBLE`				Y
Character Types:
`CHARACTER` \ `CHAR`	Y	Y	Y	Y		Y	Y
`CHAR VARYING`	Y	Y					Y
`VARCHAR`		Y				Y	Y
`VARCHAR2`				Y
`TEXT`			Y		Y	Y	Y
`LONG`				Y
`CLOB`	Y	Y		Y
National Character Types:
`NCHAR`	Y	Y		Y
`NVARCHAR`		Y
`NVARCHAR2`				Y
`NTEXT`			Y
`NCLOB`	Y	Y		Y
Character Set:
`CHARACTER SET`	Y					Y
Collation:
`COLLATE`	Y	Y	Y	Y	Y	Y	Y
Binary Types:
`BIT`	Y						Y
`BIT VARYING`	Y						Y
`BINARY`		Y	Y			Y
`BINARY VARYING`		Y
`VARBINARY`		Y	Y			Y
`BYTEA`							Y
`BLOB`	Y	Y		Y	Y	Y
`DBBLOB`		Y
`RAW`				Y
`LONG RAW`				Y
`IMAGE`			Y
Temporal Types:
`DATE`	Y	Y	Y	Y		Y	Y
`TIME`	Y	Y	Y			Y	Y
`TIME WITH TIME ZONE`	Y						Y
`DATETIME`				Y		Y
`DATETIME2`			Y
`TIMESTAMP`	Y	Y		Y		Y	Y
`TIMESTAMP WITH TIME ZONE`	Y			Y			Y
`DATETIMEOFFSET`			Y
`SMALLDATETIME`			Y
`YEAR`						Y
Collection Types:
`ARRAY`	Y			Y			Y
`ENUM`						Y	Y
`SET`						Y
Reference Types:
`REF`	Y
`ROWID`				Y
`UROWID`				Y
Object Types:
`JSON`			Y	Y			Y
`XML`		Y					Y
Other Types:
`CIDR` \ `INET` \ `MACADDR`							Y
`UUID`							Y
`UNIQUEIDENTIFIER`			Y				Y

Column Options
`NOT NULL`		Y	Y	Y	Y	Y	Y
`INVISIBLE` \ `HIDDEN`		Y		Y	Y	Y
`PRIMARY KEY`		Y	Y		Y	Y	Y
`UNIQUE`		Y	Y		Y	Y	Y
`CHECK`		Y	Y	Y	Y	Y	Y
`DEFAULT`		Y	Y	Y	Y	Y	Y
`SECURED WITH` \ `ENCRYPT` \ `ENCRYPTED WITH`		Y	Y	Y
`COMMENT`					Y	Y	Y
Generated Column
`VIRTUAL` \ `GENERATED ALWAYS AS`		Y	Y	Y	Y	Y	Y
`STORED` \ `VIRTUAL`				Y	Y	Y	Y
`PERSISTED`			Y
Identity Options
`AUTO_INCREMENT`					Y	Y
`GENERATED ALWAYS`		Y		Y			Y
`GENERATED BY DEFAULT`		Y		Y
`IDENTITY`		Y	Y
`START WITH`		Y	Y	Y
`INCREMENT BY`		Y	Y	Y
Table Options
`COMMENT`						Y
`COMPRESS` \ `COMPRESSION` \ `DATA_COMPRESSION`		Y	Y	Y		Y
`TABLESPACE`		Y	Y	Y		Y	Y
Index Options
`INCLUDE`			Y				Y

Footnotes:

Not all vendor specific data types are mapped out. If there is a missing data type that you care about do contact me with a link to your research.
The above are extracted out from the following:

ANSI-99: https://sql-99.readthedocs.io/en/latest/index.html
DB2: https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-table ^{v 11.5}
Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLE.html ^{v 21c}
MS-SQL: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16 ^{v 16}
SQLite: https://www.sqlite.org/lang_createtable.html ^{v 3.45}
MySQL: https://dev.MySQL.com/doc/refman/8.4/en/data-types.html ^{v 8.4}
PostgreSQL: https://www.postgresql.org/docs/current/sql-createtable.html ^{v 16}
JPA (ORM): https://jakarta.ee/specifications/persistence/3.2/jakarta-persistence-spec-3.2 ^{v 3.2}
- Pros/Cons: https://stackoverflow.com/questions/4406310/why-use-jpa-instead-of-writing-the-sql-query-using-jdbc
SQLAlchemy: https://docs.sqlalchemy.org/en/20/dialects/index.html ^{v 2.0}
Reference: https://www.databasestar.com/sql-data-types/

Table/Column name length

Different DBMS have different length for their identifier name with the following variance:
- DBMS Max Length
  
  DB2 128
  
  MS-SQL 128
  
  Oracle 30
  
  SQLite >= 128
  
  MySQL 64
  
  PostgreSQL 63
- If you are designing a data model to be portable between different DBMS, you should consider a identifier name no longer than 30 characters.

Maximum row size

Depending on the pagesize, the default maximum row size is as follows:
- DBMS Max Size
  
  DB2 2 Gb
  
  MS-SQL 8,060 b*
  
  Oracle 4 Gb
  
  SQLite 1 Gb
  
  MySQL 65,535 b*
  
  PostgreSQL 1.6 Tb
- Be cautious when using fixed length character or binary data type.

Data Types

Always pick the data type that is adequate to store your values. It is not just a syntax requirement, it is part of your documentation.

For example, a SMALLINT data type is more than sufficient to be the primary key for a table that stores all the countries on the planet. It conveys a maximum ceiling for ID values of 32,767 and not 2,147,483,647. In this example, it should be further constrain to a maximum of 200 with the following:
- ID SMALLINT NOT NULL CHECK( ID BETWEEN 1 AND 200 )
- SQLite uses type affinity. SEE: https://www.sqlite.org/datatype3.html#affinity
  - Any name for the data type will be accepted.

Boolean:

The following are data type for boolean with the following variance:
- DBMS Type
  
  DB2 BOOLEAN
  
  MS-SQL BIT
  
  Oracle NUMBER(1)
  
  SQLite INTEGER
  
  MySQL BOOLEAN
  
  PostgreSQL BOOLEAN
- DBMS that does not have an explicit Boolean data type should use an integer, of precision one or storage size of one byte, constraint with CHECK( colName IN(0 ,1 ,NULL)).

Integer:

All DBMS listed above support signed integer data type with the following variance:

DBMS	i8	u8	i16	u16	i32	u32	i64	u64	i128	u128
DB2	`SMALLINT`	`SMALLINT`	`SMALLINT`	`INTEGER`	`INTEGER`	`DECIMAL(10)`	`DECIMAL(20)`	`DECIMAL(20)`	`DECIMAL(31)`	`DECIMAL(31)`
MS-SQL	`TINYINT`	`SMALLINT`	`SMALLINT`	`INTEGER`	`INTEGER`	`BIGINT`	`BIGINT`	`NUMERIC(20)`	`NUMERIC(31)`	`NUMERIC(31)`
Oracle	`SMALLINT`	`SMALLINT`	`SMALLINT`	`INTEGER`	`INTEGER`	`NUMBER(10)`	`NUMBER(20)`	`NUMBER(20)`	`NUMBER(31)`	`NUMBER(31)`
SQLite	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`
MySQL	`TINYINT`	`TINYINT`	`TINYINT`	`INTEGER`	`INTEGER`	`BIGINT`	`BIGINT`	`DECIMAL(20)`	`DECIMAL(31)`	`DECIMAL(31)`
PostgreSQL	`SMALLINT`	`SMALLINT`	`SMALLINT`	`INTEGER`	`INTEGER`	`BIGINT`	`BIGINT`	`NUMERIC(20)`	`NUMERIC(31)`	`NUMERIC(31)`

Unsigned integer will need to be promoted to a larger storage and the value be checked.
- At a minimum the value to be persisted shoud be check to be a positive with CHECK( colName >= 0 ).

DBMS that does not support a smaller data type will need to be promoted to use the next data type, that have enough storage for the value, with a CHECK constraint to accept only the value range for the originally intended smaller data type.

Type	Storage	Min Value	Max Value	Max Unsigned Value
`TINYINT`	1 byte	-128	127	255
`SMALLINT`	2 bytes	-32,768	32,767	65,535
`MEDIUMINT`	3 bytes	-8,388,608	8,388,607	16,777,215
`INTEGER`	4 bytes	-2,147,483,648	2,147,483,647	4,294,967,295
`BIGINT`	8 bytes	-9,223,372,036,854,775,808	9,223,372,036,854,775,807	18,446,744,073,709,551,615
`SMALLMONEY`	4 bytes	-214,748`.`3648	214,748`.`3647
`MONEY`	8 bytes	-922,337,203,685,477`.`5808	922,337,203,685,477`.`5807
`SMALLSERIAL`	2 bytes	1	32,767
`SERIAL`	4 bytes	1	2,147,483,647
`BIGSERIAL`	8 bytes	1	9,223,372,036,854,775,807

e.g. DBMS that do not support TINYINT but has SMALLINT, the SMALLINT data type shall be used in lieu of TINYINT.
SERIAL data type are auto incremented INTEGER.
- MySQL alias SERIAL as BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Don't oversize your data type to store a small number. Right size your data type with a little room to grow but no more.
- Do your analysis or profile your data before deciding on a "right" data type for your column.
- Do constraint the column with CHECK to the expected bound of your data.

Fixed Point Number:

All DBMS listed above support fixed point data type with the following variance:

DBMS	Type	Max p	Default
DB2	`DECIMAL[(` p `[ ,` s`])]`	31	5
MS-SQL	`DECIMAL[(` p `[ ,` s`])]`	38	18
Oracle	`NUMBER [(` p `[ ,` s`])]`	38	38
SQLite	`NUMERIC[(` p `[ ,` s`])]`		?
MySQL	`DECIMAL[(` p `[ ,` s`])]`	65	10
PostgreSQL	`NUMERIC[(` p `[ ,` s`])]`		1000

where the elements between the brackets are optional.
p denotes the precision. The precision integer specifies the total number of digits.
s denotes the scale. The scale integer specifies the number of digits to the right of the decimal point.

It is good practice to specify the precision and scale because different DBMS have different default precision.
Do constraint the column with CHECK to the expected bound of your data.

Floating Point Number:

All DBMS listed above support this data type with the following variance:

FLOAT data type storage depends on the precision p.

DBMS	4 Bytes single precision	8 Bytes double precision	`FLOAT[(`p`)]`
DB2	`REAL`	`DOUBLE`	`REAL` : 01 <= p <= 24 `DOUBLE` : 25 <= p <= 53
MS-SQL	`REAL`	`FLOAT`	`REAL` : 01 <= p <= 24 `FLOAT` : 25 <= p <= 53
Oracle	`BINARY_FLOAT`	`BINARY_DOUBLE`	`FLOAT`: 01 <= p <= 24 `DOUBLE` : 25 <= p <= 53
SQLite		`REAL`
MySQL	`FLOAT`	`DOUBLE`	`FLOAT`: 00 <= p <= 24 `DOUBLE` : 25 <= p <= 53
PostgreSQL	`REAL`	`DOUBLE PRECISION`	`REAL` : 01 <= p <= 24 `DOUBLE` : 25 <= p <= 53

where the elements between the brackets are optional.
p denotes the precision in bits.

Do constraint the column with CHECK to the expected bound of your data.

Character Types:

The total storable character is dependent on the character set setup with for the database.

Character data types are the most varied in their default and maximum size among all the mentioned DBMS.
- Some DBMS default to LATIN1 which uses one byte for one character.
Do configure your database to enable UTF-8 character set.
- Optionally for business application, do consider case insensitive collation.

DBMS	Command
DB2	`CREATE DATABASE` myDBMS `USING CODESET` UTF-8 `TERRITORY` US `COLLATE USING` UCA500R1_CLDR120_CI_AI;
MS-SQL	`CREATE DATABASE` myDBMS `COLLATE` Latin1_General_100_CI_AI_SC_UTF8;
Oracle	`CREATE DATABASE` myDBMS `CHARACTER SET` AL32UTF8 `COLLATE` UTF8_CI; `SET NLS_SORT =` 'GENERIC_M_CI_AI'; `SET NLS_COMP =` 'LINGUISTIC';
SQLite	`PRAGMA encoding=`'UTF-8'; `PRAGMA case_sensitive_like =` OFF;
MySQL	`CREATE DATABASE` myDBMS `COLLATE` utf8mb4 `COLLATE` [utf8mb4_0900_ai_ci \| utf8mb4_unicode_ci];
PostgreSQL	`CREATE DATABASE` myDBMS `ENCODING` 'UTF8' `LC_COLLATE =` 'en_US.utf8', `LC_CTYPE =` 'en_US.utf8';

All DBMS listed above support fixed and variable character type with the following variance:

DBMS	Fixed Type	Fix Max s	Default	Variable Type	Var Max s	Default
DB2	`CHAR[(` s `)]`	255	1	`VARCHAR(` s `)`	32,672	1
MS-SQL	`CHAR[(` s `)]`	8,000	1	`VARCHAR[(` s `\| MAX )]`	8,000	1
Oracle	`CHAR(` s `)`	2,000		`VARCHAR2(` s `)`	4,000
SQLite	`CHAR(` s `)`	2,000		`VARCHAR(` s `)`
MySQL	`CHAR(` s `)`	255		`VARCHAR(` s `)`	16,381
PostgreSQL	`CHAR(` s `)`	10,485,760		`VARCHAR(` s `)`	10,485,760

where the elements between the brackets are optional.
s denotes the length.

The number of character will be depended on the character set setup for the database.
It is good practice to specify the length because different DBMS have different default length.

The following are data type for very large character string with the following variance:

DBMS	Type	Max s	Default
DB2	`CLOB(` s `)`	2,147,483,647	1,048,576
MS-SQL	`TEXT` \ `VARCHAR( MAX )`		2,147,483,647
Oracle	`CLOB`		4,294,967,295
SQLite	`TEXT`
MySQL	`LONGTEXT`		4,294,967,295
PostgreSQL	`TEXT`		1,063,256,064

s denotes the length.

Binary Types:

All DBMS listed above support this binary data type with the following variance:

DBMS	Fixed Type	Fix Max s	Default	Variable Type	Var Max s	Default
DB2	`BINARY(` s `)`	255	1	`VARBINARY(` s `)`	32,672	1
MS-SQL	`BINARY[(` s `)]`	8,000	1	`VARBINARY[(` s `\| MAX )]`	8,000	1
Oracle	`RAW(` s `)`	2,000		`LONG RAW`		2,147,483,648
SQLite				`BLOB`		1,000,000,000
MySQL	`BINARY(` s `)`	255	1	`VARBINARY(` s `)`	65,531
PostgreSQL				`BYTEA`		1,063,256,064

s denotes the length

The following are data type for large binary data with the following variance:

DBMS	Type	Max s	Default
DB2	`BLOB(` s `)`	2,147,483,647	1,048,576
MS-SQL	`VARBINARY[(` s \| `MAX )]`	8,000	2,147,483,647
Oracle	`BLOB`		4,294,967,295
SQLite	`BLOB`		1,000,000,000
MySQL	`LONGBLOB`		4,294,967,295
PostgreSQL	`BYTEA`		1,063,256,064

s denotes the length
MS_SQL VARBINARY( MAX ) will allow a larger storage size.

National Character

The term national character set refers to an alternative character set that enables you to store Unicode character data in a database that does not have a Unicode database character set. It is better that you setup your database to default to UTF-8 character set.

DBMS	Fixed Type	Max s	Default	Variable Type	Var Max s
DB2	`NCHAR[(` s `)]`	63	1	`NVARCHAR(` s `)`	8,168
MS-SQL	`NCHAR[(` s `)]`	4,000		`NVARCHAR(` s `)`	4,000
Oracle	`NCHAR[(` s `)]`	1,000	1	`NVARCHAR2(` s `)`	2,000
SQLite	`TEXT`			`TEXT`
MySQL	`NCHAR(` s `)`	255	1	`NVARCHAR(` s `)`	65,535
PostgreSQL	`CHAR(` s `)`	10,485,760	1	`VARCHAR(` s `)`	10,485,760

s denotes the length.
National Character set uses 2 bytes to store a character and may not be storage wise efficient.

It is good practice to specify the length because different DBMS have different default length.

The following are data type for large character data with the following variance:

DBMS	Type	Max s	Default
DB2	`NCLOB[(` s `)`	53,687,091	1,048,576
MS-SQL	`NTEXT` \ `NVARCHAR( MAX )`		1,073,741,823
Oracle	`NCLOB`		4,294,967,295
SQLite	`TEXT`
MySQL	`LONGTEXT`		4,294,967,295
PostgreSQL	`TEXT`		1,063,256,064

s denotes the length.

Temporal Types:

All DBMS listed above support temporal data type with the following variance:

DBMS	Date Only Type	Time Only Type	Date w Time	Timestamp Type	Max p	Default p	`WITH TIME ZONE`
DB2	`DATE`	`TIME`		`TIMESTAMP(` p `)`	12	6
MS-SQL	`DATE`	`TIME(` p `)`	`DATETIME`	`DATETIME2(` p `)`	7	7	`DATETIMEOFFSET`
Oracle	`DATE`			`TIMESTAMP(` p `)`	6	6	Y
MySQL	`DATE`	`TIME(` p `)`	`DATETIME`	`TIMESTAMP(` p `)`	6	6
PostgreSQL	`DATE`	`TIME(` p `)`	`DATETIME`	`TIMESTAMP(` p `)`	6	6	Y

p denotes the precision of the fractional second.
p Precision Second

0 No sub second 0

1 100 ms 0.1

2 10 ms 0.01

3 1 ms 0.001

4 100 us 0.0001

5 10 us 0.00001

6 1 us 0.000001

If you have a needed to support multiple timezones, you should consider storing all dates in UTC.
MS-SQL value range:
- DATETIME range from 1753-01-01 to 9999-12-31.
- DATETIME2 range from 0001-01-01 00:00:00.000000 to 9999-12-31 23:59:59.9999999.
- SMALLDATETIME range from 1900-01-01 00:00:00 to 2079-06-06 23:23:59.
MySQL value range:
- DATETIME range from 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.499999.
- TIMESTAMP range from 1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.499999.

It is common for business application to track the last time a row was updated. Either a DATETIME or TIMESTAMP type can be used depending on how granular your business want to capture that moment in time.

Collection Types

This data type is not that common. If cross DBMS portability is important, you should find another way to implement collection.
- DBMS Type
  
  DB2 ARRAY
  
  MS-SQL
  
  Oracle User Defined Type
  
  SQLite
  
  MySQL SET
  
  PostgreSQL ARRAY
- Oracle implement an array data type as a user defined table type. The example from Oracle's documentation is as follows:
```
CREATE  TYPE my_customers   AS  OBJECT (
        cust_name       VARCHAR2(25)
)
;
CREATE  TYPE  customer_list AS  TABLE OF  my_customers
;
CREATE  TABLE business_contacts (
        company_name    VARCHAR2(25),
        company_reps    customer_list
)
;
```

Object Types

All DBMS listed above support JSON character type with the following variance:
- DBMS Type
  
  DB2 JSON
  
  MS-SQL NVARCHAR(MAX) JSON
  
  Oracle VARCHAR2(4000) CHECK (json_data IS JSON)
  
  SQLite TEXT
  
  MySQL JSON
  
  PostgreSQL JSON \ JSONB

Other Types

All DBMS listed above support UUID character type with the following variance:
- DBMS Type
  
  DB2 UUID
  
  MS-SQL UNIQUEIDENTIFIER
  
  Oracle RAW(16)
  
  SQLite BLOB
  
  MySQL UUID
  
  PostgreSQL UUID

Column Options

DBMS specific ordering of column options.

DBMS	Column Definition
DB2	colName type `[NOT NULL] [UNIQUE\|PRIMARY KEY]` `[CHECK(` condition `)] [DEFAULT` value `] [SECURED WITH` label `] [IMPLICITLY HIDDEN]`
MS-SQL	colName type `[NOT NULL] [DEFAULT` value `] [HIDDEN]` `[ENCRYPTED WITH(` spec `)] [CHECK(` condition `)] [UNIQUE\|PRIMARY KEY]`
Oracle	colName type `[INVISIBLE] [DEFAULT` value `] [ENCRYPT` spec `] [NOT NULL] [CHECK(` condition `)] [UNIQUE\|PRIMARY KEY]`
SQLite	colName type `[NOT NULL] [DEFAULT` value `] [HIDDEN]` `[CHECK(` condition `)] [UNIQUE\|PRIMARY KEY]`
MySQL	colName type `[NOT NULL] [DEFAULT` value `] [INVISIBLE]` `[CHECK(` condition `)] [UNIQUE\|PRIMARY KEY] [COMMENT =` 'string' `]`
PostgreSQL	colName type `[NOT NULL] [DEFAULT` value `] [CHECK(` condition `)] [UNIQUE\|PRIMARY KEY]`

Generated Column

DBMS specific ordering of generated column options.

DBMS	Column Definition
DB2	colName type `GENERATED ALWAYS AS(` expr `)`
MS-SQL	colName `AS(` expr `) [PERSISTED]`
Oracle	colName type `GENERATED ALWAYS AS(` expr `) VIRTUAL`
SQLite	colName type `GENERATED ALWAYS AS(` expr `) [VIRTUAL\|STORED]`
MySQL	colName type `GENERATED ALWAYS AS(` expr `) [VIRTUAL\|STORED]`
PostgreSQL	colName type `GENERATED ALWAYS AS(` expr `) STORED`

Oracle virtual column cannot be stored.
PostgreSQL does not have virtual column. Generated column is concrete and the data is stored in the row.

Identity Options

DBMS specific identity column options.

DBMS	Column Definition
DB2	colName type `GENERATED BY DEFAULT AS IDENTITY( START WITH` start `INCREMENT BY` incr `) PRIMARY KEY`
MS-SQL	colName type `IDENTITY(` start ,increment `) PRIMARY KEY`
Oracle	colName type `GENERATED BY DEFAULT AS IDENTITY( START WITH` start `INCREMENT BY` incr `) PRIMARY KEY`
SQLite	colName type `PRIMARY KEY AUTOINCREMENT`
MySQL	colName type `AUTO_INCREMENT PRIMARY KEY`
PostgreSQL	colName type `GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY` incr `START WITH` start `)`

Identity value defaults to start at 1. However, I recommend that it start at higher number depending on the data type. 10 or 100 are good candidates. Starting at a higher number is akin to the stack memory where it is reserved for system seeded values.
SQLite start value_ need to be updated in the system table sqlite_sequence as follows:
- UPDATE sqlite_sequence SET seq = 100 WHERE NAME = 'tableName';
MySQL start and incr value need to be set at the database level with:
- ALTER TABLE tabName AUTO_INCREMENT = start;
- SET [ GLOBAL | SESSION ] auto_increment_increment= incr;
GENERATED BY DEFAULT will generate an the next value when no explicit value is given where as GENERATED ALWAYS will generate an the next value on every insertion
Alternate to a DBMS generated sequential unique integer ID, application generated ID using UUID have come to be widely use. Both serve very similar purpose but your business dictates the goal. Here are my guidelines:
- Use ID if you have a traditional centralized DBMS setup.
  - ID is much more efficient in storage requirement and impact indices build using it. e.g. 4 byte versus 16 byte for UUID.
- Use UUID if you have a distributed master-master DBMS setup where inserts are completely independent and unique.
  - UUID is rather random and do not have the linear order of an ID.
  - UUID is much less efficient is storage requirement and impact indices build using it.
  - If you have a small number of distributed DBMS, you could consider using interleave ID with an increment that is larger than 1 starting with a different offset for each of your DBMS.

Returning Generated ID

DBMS specific implementation for a two column table with a primary key column named ID of type integer and the other column named C1 of type float:

DB2

INSERT  INTO T( C1 )
VALUES( 1.23 );
SELECT  IDENTITY_VAL_LOCAL()
FROM    sysibm.sysdummy1;

MS-SQL:

INSERT  INTO T( C1 )
VALUES( 1.23 );
SELECT  SCOPE_IDENTITY();

Oracle:

INSERT  INTO T( C1 )
VALUES( 1.23 )
RETURNING ID
INTO      var_id;

SQLite:

INSERT  INTO T( C1 )
VALUES( 1.23 );
SELECT  LAST_INSERT_ROWID();

MySQL:

INSERT  INTO T( C1 )
VALUES( 1.23 );
SELECT  LAST_INSERT_ID();

PostgreSQL:

INSERT  INTO T( C1 )
VALUES( 1.23 )
RETURNING ID;

DBMS specific column comment.

DBMS	Column Definition
DB2	`COMMENT ON COLUMN` tabName.colName `IS` 'string'
MS-SQL	`EXEC sp_addextendedproperty @name = N'MS_Description' ,@value = N`'string' `,@level1type = N'Table' ,@level1name =` tabName `,@level2type = N'Column', @level2name =` colName
Oracle	`COMMENT ON COLUMN` tabName.colName `IS` 'string'
SQLite
MySQL	colName type `COMMENT` 'string'
PostgreSQL	`COMMENT ON COLUMN` tabName.colName `IS` 'string'

Table Options

DBMS specific table space options.

DBMS	Column Definition
DB2	`CREATE TABLE` tabName `(...) IN` tabSpaceName `[COMPRESS YES]`
MS-SQL	`CREATE TABLE` tabName `(...) [WITH (DATA_COMPRESSION = PAGE)]` `ON` fileGroup_name
Oracle	`CREATE TABLE` tabName `(...) TABLESPACE` tabSpaceName `[COMPRESS]`
SQLite
MySQL	`CREATE TABLE` tabName `(...) TABLESPACE` tabSpaceName
PostgreSQL	`CREATE TABLE` tabName `(...) TABLESPACE` tabSpaceName

MySQL cannot created a compressed table in a user defined tablespace.
- A compressed table can be created in the default tablespace with the following table option:
- [ENGINE=InnoDB ROW_FORMAT=COMPRESSED]
PostgreSQL automatically compresses large values stored in tables using TOAST. This applies to TEXT, BYTEA, and XML data types by default.
- You can enable custom zstd compression via pgzstd extension.
  - CREATE EXTENSION pg_zstd;
  - ALTER TABLE compressed_table ALTER COLUMN bigDataCol SET STORAGE EXTENDED;
  - ALTER TABLE compressed_table ALTER COLUMN bigDataCol SET COMPRESSION 'zstd';

DBMS specific table comment.

DBMS	Column Definition
DB2	`COMMENT ON TABLE` tabName `IS` 'string'
MS-SQL	`EXEC sp_addextendedproperty @name = N'MS_Description' ,@value = N`'string' `,@level1type = N'Table' ,@level1name =` tabName
Oracle	`COMMENT ON TABLE` tabName `IS` 'string'
SQLite
MySQL	`CREATE TABLE` tabName `(...) COMMENT =` 'string'
PostgreSQL	`COMMENT ON TABLE` tabName `IS` 'string'

Index Options

DBMS specific table index options.

DBMS	Column Definition
DB2	`CREATE [UNIQUE] INDEX` idxName `ON` tabName`(`col `[DESC] [,..n] )` `[IN` tabSpaceName`] [INCLUDE(`col `[,..n])]` `[COMPRESS [YES\|NO]]`
MS-SQL	`CREATE [UNIQUE] [CLUSTERED] INDEX` idxName `ON` tabName`(`col `[DESC] [,..n] )` `[INCLUDE(`col `[,..n])]` `[WHERE` expr `] [WITH( DATA_COMPRESSION=PAGE )] [ON fileGroup]`
Oracle	`CREATE [UNIQUE\|BITMAP] INDEX` idxName `ON` tabName`(`col `[DESC] [,..n] )` `[TABLESPACE` tabSpaceName`] [COMPRESS [ADVANCED]] [PARALLEL\|NOPARALLEL]`
SQLite	`CREATE [UNIQUE] [IF NOT EXISTS] INDEX` idxName `ON` tabName`(`col `[DESC] [,..n] )` `[WHERE` expr`]`
MySQL	`CREATE [UNIQUE] [IF NOT EXISTS] INDEX` idxName `ON` tabName`(`col `[DESC] [,..n] )`
PostgreSQL	`CREATE [UNIQUE] [IF NOT EXISTS] INDEX` idxName `ON` tabName`(`col `[DESC] [,..n] )` `INCLUDE(`col `[,..n])] [TABLESPACE` tabSpace `] [WHERE expr]`

The BITMAP and WHERE options are not well supported across our listed DBMSs. These options should be avoided unless you have a valid case to use them.
The INCLUDE option can be very useful in filtering rows without incurring the seek back to the table. DBMS that do not support this option can create a multi column non-unique index by appending the additional columns to it. If your intention for the index is to be unique, the additional appended/included columns will change your original definition of unique.

Recommendations for ORM frameworks

To be compatible across these DBMS, unfortunately, we should code to the lowest common denominator when we map from a programming language into a table:

Primitives Type	C	C#	GO	Java	Python	Rust	V	Zig	DB2	MS-SQL	Oracle	SQLite	MySQL	PostgreSQL
bool \ boolean	Y	Y	Y	Y	Y	Y	Y	Y	`BOOLEAN`	`BIT`	`NUMBER(1)`	`INTEGER`	`BOOLEAN`	`BOOLEAN`
byte		Y	Y	Y					`GRAPHIC(1)`	`VARBINARY(1)`	`RAW(1)`	`BLOB`	`BINARY(1)`	`BIT(1)`
sbyte		Y							`SMALLINT`	`TINYINT`	`SMALLINT`	`INTEGER`	`TINYINT`	`SMALLINT`
char ¹	Y								`CHAR(1)`	`CHAR(1)`	`CHAR(1)`	`TEXT`	`CHAR(1)`	`CHAR(1)`
char ²		Y		Y		Y			`CHAR(2)`	`CHAR(2)`	`CHAR(2)`	`TEXT`	`CHAR(2)`	`CHAR(2)`
i8			Y			Y	Y	Y	`SMALLINT`	`TINYINT`	`SMALLINT`	`INTEGER`	`TINYINT`	`SMALLINT`
u8 ^a			Y			Y	Y	Y	`SMALLINT`	`SMALLINT`	`SMALLINT`	`INTEGER`	`SMALLINT`	`SMALLINT`
i16			Y			Y	Y	Y	`SMALLINT`	`SMALLINT`	`SMALLINT`	`INTEGER`	`SMALLINT`	`SMALLINT`
u16 ^b			Y			Y	Y	Y	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`
i32			Y			Y	Y	Y	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`
u32 ^c			Y			Y	Y	Y	`DECIMAL(10)`	`BIGINT`	`NUMBER(10)`	`INTEGER`	`BIGINT`	`BIGINT`
i64			Y			Y	Y	Y	`DECIMAL(20)`	`BIGINT`	`NUMBER(20)`	`INTEGER`	`BIGINT`	`BIGINT`
u64 ^d			Y			Y	Y	Y	`DECIMAL(20)`	`NUMERIC(20)`	`NUMBER(20)`	`INTEGER`	`DECIMAL(20)`	`NUMERIC(20)`
i128						Y	Y	Y	`DECIMAL(31)`	`NUMERIC(31)`	`NUMBER(31)`	`INTEGER`	`DECIMAL(31)`	`NUMERIC(31)`
u128 ^e						Y	Y	Y	`DECIMAL(31)`	`NUMERIC(31)`	`NUMBER(31)`	`INTEGER`	`DECIMAL(31)`	`NUMERIC(31)`
short	Y	Y		Y					`SMALLINT`	`SMALLINT`	`SMALLINT`	`INTEGER`	`SMALLINT`	`SMALLINT`
ushort ^b		Y							`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`
unsigned short ^b									`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`
int	Y	Y	Y	Y	Y		Y		`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`	`INTEGER`
uint ^c		Y	Y						`DECIMAL(10)`	`NUMERIC(10)`	`NUMBER(10)`	`NUMBER(10)`	`DECIMAL(10)`	`BIGINT`
unsigned int ^c	Y								`DECIMAL(10)`	`NUMERIC(10)`	`NUMBER(10)`	`NUMBER(10)`	`DECIMAL(10)`	`BIGINT`
long	Y	Y		Y					`DECIMAL(20)`	`NUMERIC(20)`	`NUMBER(20)`	`NUMBER(20)`	`DECIMAL(20)`	`NUMBER(20)`
ulong ^e		Y							`DECIMAL(20)`	`NUMERIC(20)`	`NUMBER(20)`	`NUMBER(20)`	`DECIMAL(20)`	`NUMBER(20)`
unsigned long ^e	Y								`DECIMAL(20)`	`NUMERIC(20)`	`NUMBER(20)`	`NUMBER(20)`	`DECIMAL(20)`	`NUMBER(20)`
long long	Y								`DECIMAL(31)`	`NUMERIC(31)`	`NUMBER(31)`	`NUMBER(31)`	`DECIMAL(31)`	`NUMBER(31)`
unsigned long long ^e	Y								`DECIMAL(31)`	`NUMERIC(31)`	`NUMBER(31)`	`NUMBER(31)`	`DECIMAL(31)`	`NUMBER(31)`
f16								Y	`REAL`	`REAL`	`BINARY_FLOAT`	`REAL`	`REAL`	`REAL`
f32 \ float32			Y			Y	Y	Y	`REAL`	`REAL`	`BINARY_FLOAT`	`REAL`	`REAL`	`REAL`
f64 \ float64			Y			Y	Y	Y	`DOUBLE`	`FLOAT`	`BINARY_DOUBLE`	`REAL`	`DOUBLE`	`DOUBLE PRECISION`
float	Y	Y		Y	Y				`REAL`	`REAL`	`BINARY_FLOAT`	`REAL`	`REAL`	`REAL`
double	Y	Y		Y					`DOUBLE`	`FLOAT`	`BINARY_DOUBLE`	`REAL`	`DOUBLE`	`DOUBLE PRECISION`
decimal		Y							`NUMERIC(p,s)`	`NUMERIC(p,s)`	`NUMBER(p,s)`	`REAL`	`DECIMAL(p,s)`	`NUMERIC(p,s)`
long double	Y								`NUMERIC(p,s)`	`NUMERIC(p,s)`	`NUMBER(p,s)`	`REAL`	`DECIMAL(p,s)`	`NUMERIC(p,s)`

Above listed DBMS do not support unsigned numbers therefore unsigned numbers from your programming language will need to be promoted to a larger storage and the value be checked before it is persisted into the table.
- ¹ 1 byte character.
- ² 2 bytes character.
- ^a 1 bytes use constraint CHECK( _colName_ BETWEEN 0 AND 255 )
- ^b 2 bytes use constraint CHECK( _colName_ BETWEEN 0 AND 65535 )
- ^c 4 bytes use constraint CHECK( _colName_ BETWEEN 0 AND 4294967295 )
- ^d 8 bytes use constraint CHECK( _colName_ BETWEEN 0 AND 18446744073709551615 )
- ^e 16 bytes use constraint CHECK( _colName_ BETWEEN 0 AND 340282366920938463463374607431768211455 )

Compound Type	DB2	MS-SQL	Oracle	SQLite	MySQL	PostgreSQL
Date	`DATE`	`DATE`	`DATE`	`TEXT`	`DATE`	`DATE`
Time	`TIME(0)`	`TIME(0)`	`INTEGER`	`TEXT`	`TIME(0)`	`TIME(0)`
Datetime	`TIMESTAMP(0)`	`DATETIME`	`TIMESTAMP(0)`	`TEXT`	`DATETIME`	`DATETIME`
Timestamp	`TIMESTAMP(6)`	`TIMESTAMP(6)`	`TIMESTAMP(6)`	`TEXT`	`TIMESTAMP(6)`	`TIMESTAMP(6)`
Short string	`VARCHAR(4000)`	`VARCHAR(4000)`	`VARCHAR2(4000)`	`VARCHAR(4000)`	`VARCHAR(4000)`	`VARCHAR(4000)`
Short binary	`VARBINARY(2000)`	`VARBINARY(2000)`	`RAW(2000)`	`BLOB`	`VARBINARY(2000)`	`BYTEA`
Long string	`CLOB(2M)`	`TEXT`	`CLOB`	`TEXT`	`LONGTEXT`	`TEXT`
Long binary	`BLOB(2M)`	`VARBINARY(MAX)`	`BLOB`	`BLOB`	`LONGBLOB`	`BYTEA`

Oracle could store the time as the number of seconds (86400) since midnight.

ORM framework should support as many features outlined above that are cross DBMS.

Name		Name	Last commit message	Last commit date
Latest commit History 28 Commits
scripts		scripts
LICENSE		LICENSE
README.md		README.md

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

Cross DBMS Table features and nuiances

Footnotes:

Table/Column name length

Maximum row size

Data Types

Boolean:

Integer:

Fixed Point Number:

Floating Point Number:

Character Types:

Binary Types:

National Character

Temporal Types:

Collection Types

Object Types

Other Types

Column Options

Generated Column

Identity Options

Returning Generated ID

DB2

MS-SQL:

Oracle:

SQLite:

MySQL:

PostgreSQL:

Table Options

Index Options

Recommendations for ORM frameworks

About

Uh oh!

Releases

Packages

Contributors 2

Uh oh!

DBMS	Max Size
DB2	2 Gb
MS-SQL	8,060 b*
Oracle	4 Gb
SQLite	1 Gb
MySQL	65,535 b*
PostgreSQL	1.6 Tb

p	Precision	Second
0	No sub second	0
1	100 ms	0.1
2	10 ms	0.01
3	1 ms	0.001
4	100 us	0.0001
5	10 us	0.00001
6	1 us	0.000001

DBMS	Type
DB2	`JSON`
MS-SQL	`NVARCHAR(MAX) JSON`
Oracle	`VARCHAR2(4000) CHECK (json_data IS JSON)`
SQLite	`TEXT`
MySQL	`JSON`
PostgreSQL	`JSON` \ `JSONB`

License

elau1004/Cross-DBMS-TabCol-Spec

Folders and files

Latest commit

History

Repository files navigation

Cross DBMS Table features and nuiances

Footnotes:

Table/Column name length

Maximum row size

Data Types

Boolean:

Integer:

Fixed Point Number:

Floating Point Number:

Character Types:

Binary Types:

National Character

Temporal Types:

Collection Types

Object Types

Other Types

Column Options

Generated Column

Identity Options

Returning Generated ID

DB2

MS-SQL:

Oracle:

SQLite:

MySQL:

PostgreSQL:

Table Options

Index Options

Recommendations for ORM frameworks

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Contributors 2

Uh oh!

Packages