SQL Data Types Reference

🔢 Numeric Types

Concept	PostgreSQL	MySQL	SQL Server	SQLite	Oracle
16-bit integer	SMALLINT	SMALLINT	SMALLINT	INTEGER	NUMBER(5)
32-bit integer	INTEGER / INT	INT	INT	INTEGER	NUMBER(10)
64-bit integer	BIGINT	BIGINT	BIGINT	INTEGER	NUMBER(19)
Auto-increment integer	SERIAL / BIGSERIAL	INT AUTO_INCREMENT	INT IDENTITY(1,1)	INTEGER PRIMARY KEY	NUMBER GENERATED ALWAYS AS IDENTITY
Fixed-point decimal	NUMERIC(p,s) / DECIMAL(p,s)	DECIMAL(p,s)	DECIMAL(p,s) / NUMERIC(p,s)	NUMERIC	NUMBER(p,s)
Single-precision float	REAL	FLOAT	REAL	REAL	BINARY_FLOAT
Double-precision float	DOUBLE PRECISION	DOUBLE	FLOAT	REAL	BINARY_DOUBLE
Money / currency	MONEY	DECIMAL(19,4)	MONEY / SMALLMONEY	NUMERIC	NUMBER(19,4)

⚠️ SQLite has no true BIGINT; it uses flexible typing. Oracle NUMBER without precision is a floating-point.

🔤 String Types

Concept	PostgreSQL	MySQL	SQL Server	SQLite	Oracle
Fixed-length char	CHAR(n)	CHAR(n)	CHAR(n)	TEXT	CHAR(n)
Variable-length string	VARCHAR(n)	VARCHAR(n)	VARCHAR(n) / NVARCHAR(n)	TEXT	VARCHAR2(n)
Unlimited/long text	TEXT	LONGTEXT	VARCHAR(MAX) / TEXT	TEXT	CLOB
Unicode string	VARCHAR / TEXT (UTF-8 native)	NVARCHAR / VARCHAR with utf8mb4	NVARCHAR(n)	TEXT (UTF-8 native)	NVARCHAR2(n)
Binary data	BYTEA	BLOB / BINARY	VARBINARY(n)	BLOB	RAW / BLOB

⚠️ Oracle recommends VARCHAR2 over VARCHAR. SQLite uses dynamic typing; TEXT accepts any length. MySQL VARCHAR max is 65,535 bytes.

📅 Date & Time Types

Concept	PostgreSQL	MySQL	SQL Server	SQLite	Oracle
Date only	DATE	DATE	DATE	TEXT / DATE affinity	DATE
Time only	TIME	TIME	TIME	TEXT / TIME affinity	DATE (with time component)
Date + time	TIMESTAMP	DATETIME	DATETIME2	TEXT / DATETIME affinity	TIMESTAMP (deprecated) / DATE
Date + time + timezone	TIMESTAMPTZ	TIMESTAMP	DATETIMEOFFSET	TEXT	TIMESTAMP WITH TIME ZONE
Auto now (current timestamp)	DEFAULT NOW()	DEFAULT CURRENT_TIMESTAMP	DEFAULT GETDATE()	DEFAULT CURRENT_TIMESTAMP	DEFAULT CURRENT_TIMESTAMP

⚠️ Oracle TIMESTAMP is deprecated for new designs; use DATE for seconds precision. SQLite stores datetimes as ISO-8601 text strings by default.

✅ Boolean & Special Types

Concept	PostgreSQL	MySQL	SQL Server	SQLite	Oracle
Boolean	BOOLEAN	BOOLEAN / TINYINT(1)	BIT	INTEGER (0 or 1)	NUMBER(1)
UUID / GUID	UUID	BINARY(16) / CHAR(36)	UNIQUEIDENTIFIER	BLOB / TEXT	RAW(16) / VARCHAR2(36)
JSON	JSON / JSONB	JSON	NVARCHAR(MAX) / JSON (2016+)	TEXT / JSON affinity	CLOB / VARCHAR2 (with JSON constraint)
Enum	CREATE TYPE ... AS ENUM	ENUM(...)	VARCHAR + CHECK	TEXT + CHECK	VARCHAR2 + CHECK
Array	INTEGER[] / TEXT[]	Not native	Not native	Not native	VARRAY / Nested Table

⚠️ MySQL BOOLEAN is an alias for TINYINT(1). SQLite has no native Boolean; use INTEGER 0/1. Oracle added native JSON support in 12c.

🔄 Common Migration Pitfalls

Pitfall	Details
BOOLEAN → MySQL	PostgreSQL BOOLEAN becomes TINYINT(1) in MySQL. Your ORM may handle this, but raw SQL migrations need care.
VARCHAR length	MySQL VARCHAR(255) is bytes, not characters with utf8mb4. PostgreSQL VARCHAR(n) is characters.
Auto-increment	SERIAL (Postgres), AUTO_INCREMENT (MySQL), IDENTITY (SQL Server), GENERATED ALWAYS (Oracle). None are compatible.
TEXT types	SQLite TEXT is unlimited. Migrating to MySQL requires choosing TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT.
Date/time precision	PostgreSQL TIMESTAMP has microsecond precision. SQL Server DATETIME2 has 100ns precision. Oracle DATE has second precision.
JSON storage	PostgreSQL JSONB is binary and indexable. MySQL JSON is binary but with different indexing rules. SQLite stores JSON as text.