SQL Dialects & Portability
Type and Generated-Value Differences
Type names are not semantic guarantees: range, precision, time-zone handling, coercion, storage, comparison, and driver conversion differ. Define application domains and round-trip tests first, then map each dialect explicitly; generated values still require uniqueness constraints and safe retrieval.
- Map domains, not spellings.
- Exact numbers need an overflow policy.
- Separate instants from civil time.
- Generated values are allocation, not identity.
- Retrieval must be statement- and scope-safe.
- Round-trip at boundaries.
CREATE TABLE customers (customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email varchar(320) NOT NULL UNIQUE);
INSERT INTO customers (email) VALUES ('[email protected]') RETURNING customer_id;| Domain | PostgreSQL | MySQL | SQLite | SQL Server | Oracle |
|---|---|---|---|---|---|
| Text | varchar/text, database encoding | varchar/text + charset/collation | TEXT, dynamic typing/affinity | varchar or nvarchar | VARCHAR2/NVARCHAR2/CLOB; empty string becomes null |
| Exact number | numeric(p,s) | decimal(p,s) | NUMERIC affinity; representation can vary | decimal(p,s) | NUMBER(p,s) |
| Instant | timestamp with time zone normalizes instant | timestamp has UTC/session conversion and limited range | no dedicated storage class; convention required | datetimeoffset | TIMESTAMP WITH TIME ZONE |
| Boolean | boolean | BOOLEAN synonym maps to integer type | 0/1 convention; TRUE/FALSE literals | bit | BOOLEAN in 26ai; qualify older-version mapping |
| Binary/large | bytea/large objects | binary/blob families | BLOB | varbinary(max) | RAW/BLOB |
| JSON | json/jsonb | JSON type | text/blob plus JSON functions | JSON representation/features depend version | JSON type/features depend version |
| UUID | uuid | binary(16) or char mapping | BLOB(16)/TEXT convention | uniqueidentifier | RAW(16) or character mapping |