Column Types Like DATETIME and BYTEA Exist on Some SQL Engines Only

A schema with DATETIME and JSONB columns runs on SQLite without complaint. Both types fail on at least one of the other engines.

SQLite accepts nearly any type name, so a schema that works there can still fail elsewhere. Each type in the table was used in a CREATE TABLE on all four engines.

CREATE TABLE t (d DATETIME); SELECT COUNT(*) FROM t, run on four engines: SQLite 3.45.1 0; PostgreSQL 16.15 error: type "datetime" does not exist; MySQL 8.0.46 0; MariaDB 10.11.14 0.

What Each Engine Answered

The example

CREATE TABLE t (d DATETIME);
SELECT COUNT(*) FROM t;

gave these answers:

Engine	Answer
SQLite 3.45.1	`0`
PostgreSQL 16.15	error: `type "datetime" does not exist`
MySQL 8.0.46	`0`
MariaDB 10.11.14	`0`

Every Form BareSQL Checks

BareSQL Portable checks 27 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form	SQLite	PostgreSQL	MySQL	MariaDB	What to write instead
`DATETIME`	yes	no	yes	yes	Use TIMESTAMP.
`TINYINT`	yes	no	yes	yes	Use SMALLINT.
`MEDIUMINT`	yes	no	yes	yes	Use INTEGER.
`DOUBLE`	yes	no	yes	yes	Use DOUBLE PRECISION.
`UNSIGNED`	yes	no	yes	yes	Use INTEGER, or BIGINT for large values, with CHECK (x >= 0).
`TINYTEXT`	yes	no	yes	yes	Use VARCHAR(255).
`MEDIUMTEXT`	yes	no	yes	yes	Use TEXT. On MySQL and MariaDB TEXT holds up to 64 KB.
`LONGTEXT`	yes	no	yes	yes	Use TEXT. On MySQL and MariaDB TEXT holds up to 64 KB.
`BLOB`	yes	no	yes	yes	No binary type works on all four: BYTEA on PostgreSQL.
`LONGBLOB`	yes	no	yes	yes	No binary type works on all four: BYTEA on PostgreSQL, BLOB on SQLite.
`ENUM`	no	no	yes	yes	Use VARCHAR(n) with CHECK (x IN ('a', 'b')).
`SET`	no	no	yes	yes	Use a separate table with one row per value.
`YEAR`	yes	no	yes	yes	Use SMALLINT.
`BYTEA`	yes	yes	no	no	No binary type works on all four: BLOB on SQLite, MySQL and MariaDB.
`TIMESTAMPTZ`	yes	yes	no	no	Use TIMESTAMP and keep times in UTC.
`TIMESTAMP WITH TIME ZONE`	yes	yes	no	no	Use TIMESTAMP and keep times in UTC.
`JSONB`	yes	yes	no	no	Use JSON on PostgreSQL, MySQL and MariaDB, and TEXT on SQLite.
`UUID`	yes	yes	no	yes	Use CHAR(36) or VARCHAR(36).
`INET`	yes	yes	no	no	Use VARCHAR(45).
`MONEY`	yes	yes	no	no	Use DECIMAL(19, 4).
`INTERVAL`	yes	yes	no	no	Store the length as a number of seconds or days.
`ARRAY`	yes	yes	no	no	Use a separate table with one row per element.
`NUMBER`	yes	no	no	no	Use DECIMAL(p, s) or INTEGER.
`VARCHAR2`	yes	no	no	no	Use VARCHAR(n).
`NVARCHAR`	yes	no	yes	yes	Use VARCHAR(n); all four store Unicode in VARCHAR.
`CLOB`	yes	no	no	no	Use TEXT.
`DATETIME2`	yes	no	no	no	Use TIMESTAMP.

What to Write Instead

This form answers the same on every engine:

CREATE TABLE t (d TIMESTAMP);
SELECT COUNT(*) FROM t;

Engine	Answer
SQLite 3.45.1	`0`
PostgreSQL 16.15	`0`
MySQL 8.0.46	`0`
MariaDB 10.11.14	`0`

Use types that all four accept: INTEGER, BIGINT, SMALLINT, DECIMAL(p, s), DOUBLE PRECISION, VARCHAR(n), TEXT, DATE, TIME, TIMESTAMP and BOOLEAN. There is no binary type they share: BLOB on SQLite, MySQL and MariaDB, BYTEA on PostgreSQL.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:19: error: DATETIME works on SQLite, MySQL and MariaDB; PostgreSQL refuses it. [type-not-everywhere]
    DATETIME
    Use TIMESTAMP.

1 problem: 1 error.

baresql rules type-not-everywhere prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.