SQL Data Types
The same idea has a different name in every database. Find the type you need and its equivalent in MySQL, PostgreSQL, SQL Server, Oracle and SQLite.
| What you want | MySQL | PostgreSQL | SQL Server | Oracle | SQLite | Notes |
|---|---|---|---|---|---|---|
| Small integer | SMALLINT | SMALLINT | SMALLINT | NUMBER(5) | INTEGER | −32,768 to 32,767 (2 bytes). |
| Integer | INT | INTEGER | INT | NUMBER(10) | INTEGER | −2,147,483,648 to 2,147,483,647 (4 bytes). The default for ids in small tables. |
| Big integer | BIGINT | BIGINT | BIGINT | NUMBER(19) | INTEGER | ±9.2 quintillion (8 bytes). Use for ids that may pass 2 billion. |
| Auto-increment id | INT AUTO_INCREMENT | INTEGER GENERATED ALWAYS AS IDENTITY (or SERIAL) | INT IDENTITY(1,1) | NUMBER GENERATED AS IDENTITY | INTEGER PRIMARY KEY | SQLite’s INTEGER PRIMARY KEY is an alias for the rowid. |
| Exact decimal | DECIMAL(p,s) | NUMERIC(p,s) | DECIMAL(p,s) | NUMBER(p,s) | NUMERIC | Exact. Use for money: DECIMAL(12,2). |
| Floating point | DOUBLE | DOUBLE PRECISION | FLOAT(53) | BINARY_DOUBLE | REAL | Approximate (IEEE 754). Never for money: 0.1 + 0.2 ≠ 0.3. |
| Single-precision float | FLOAT | REAL | REAL | BINARY_FLOAT | REAL | About 7 significant digits. |
| Money | DECIMAL(19,4) | NUMERIC(19,4) | DECIMAL(19,4) | NUMBER(19,4) | INTEGER (cents) | PostgreSQL MONEY and SQL Server MONEY exist but are locale- or precision-limited; prefer DECIMAL or integer cents. |
| Boolean | BOOLEAN (TINYINT(1)) | BOOLEAN | BIT | BOOLEAN (23ai) / NUMBER(1) | INTEGER 0/1 | Oracle added a SQL BOOLEAN type in 23ai. |
| Variable-length text | VARCHAR(n) | VARCHAR(n) / TEXT | NVARCHAR(n) | VARCHAR2(n) | TEXT | In PostgreSQL VARCHAR(n) and TEXT perform the same; n is just a check. |
| Fixed-length text | CHAR(n) | CHAR(n) | NCHAR(n) | CHAR(n) | TEXT | Padded with spaces. Only for truly fixed codes (ISO country codes). |
| Long text | TEXT / LONGTEXT | TEXT | NVARCHAR(MAX) | CLOB | TEXT | MySQL TEXT holds 64 KB; MEDIUMTEXT 16 MB; LONGTEXT 4 GB. |
| Unicode text | VARCHAR with utf8mb4 | TEXT (UTF-8 database) | NVARCHAR | NVARCHAR2 or AL32UTF8 charset | TEXT | In MySQL use utf8mb4, not utf8 (which cannot store emoji). |
| Enumeration | ENUM('a','b') | CREATE TYPE … AS ENUM | VARCHAR + CHECK | VARCHAR2 + CHECK | TEXT + CHECK | A lookup table with a foreign key is more flexible than an enum. |
| UUID | BINARY(16) / CHAR(36) | UUID | UNIQUEIDENTIFIER | RAW(16) | TEXT / BLOB | Time-ordered UUIDv7 indexes far better than random UUIDv4. |
| JSON | JSON | JSONB | NVARCHAR(MAX) / JSON (2025) | JSON (21c+) | TEXT (json functions) | PostgreSQL JSONB is binary, indexable with GIN; JSON keeps the raw text. |
| Date | DATE | DATE | DATE | DATE | TEXT (ISO 8601) | Oracle DATE also stores a time of day to the second. |
| Time of day | TIME | TIME | TIME | INTERVAL DAY TO SECOND | TEXT | |
| Date and time | DATETIME | TIMESTAMP | DATETIME2 | TIMESTAMP | TEXT (ISO 8601) | No time zone stored. |
| Timestamp with time zone | TIMESTAMP (stored as UTC) | TIMESTAMPTZ | DATETIMEOFFSET | TIMESTAMP WITH TIME ZONE | TEXT | Best default for “when did it happen”. MySQL TIMESTAMP ends in 2038. |
| Interval / duration | (none, store seconds) | INTERVAL | (none, store seconds) | INTERVAL | INTEGER | |
| Binary data | BLOB / VARBINARY(n) | BYTEA | VARBINARY(MAX) | BLOB / RAW(n) | BLOB | Store large files in object storage and keep the URL in the database. |
| Array | (none, use JSON) | INTEGER[] / TEXT[] | (none) | VARRAY / nested table | (none) | Usually a child table is the relational answer. |
| Spatial | GEOMETRY / POINT | POINT (PostGIS: geometry) | GEOGRAPHY / GEOMETRY | SDO_GEOMETRY | (SpatiaLite) |
Choosing a data type: five rules
- Pick the narrowest type that will always fit. Smaller types mean smaller indexes and more rows per page, but never so narrow that real data is truncated.
- Exact numbers for exact things. Money, quantities and percentages go in DECIMAL/NUMERIC or integers. Floats are for measurements.
- Store moments in UTC with a time zone-aware type and convert when displaying.
- Identifiers that look like numbers are text. Phone numbers, ZIP codes and card numbers have leading zeros and are never added together.
- Match foreign key types to the key they reference. A BIGINT key referenced by an INT column will fail or force implicit casts in joins.
The CSV to SQL converter applies these rules when it infers column types, and the DBML editor maps types when converting a schema between PostgreSQL, MySQL, SQL Server and SQLite. The type mappings above follow each vendor’s official documentation.
Questions
What are the main SQL data types?
Numeric (INTEGER, BIGINT, DECIMAL/NUMERIC, FLOAT), character (CHAR, VARCHAR, TEXT), date and time (DATE, TIME, TIMESTAMP), boolean, and binary (BLOB/BYTEA). Most databases add JSON, UUID, and spatial types.
What is the difference between VARCHAR and TEXT?
VARCHAR(n) caps the length at n characters; TEXT has no declared limit. In PostgreSQL they are stored and perform identically. In MySQL, TEXT columns are stored off-page, cannot have a default value before 8.0.13, and need a prefix length to be indexed, so VARCHAR is preferred for short strings.
Which data type should I use for money?
An exact type: DECIMAL(19,4) or NUMERIC(12,2), or an integer number of cents. Never FLOAT or DOUBLE, which store binary approximations and produce rounding errors such as 0.1 + 0.2 = 0.30000000000000004.
Should I store dates as timestamps with time zone?
For moments in time (created_at, paid_at), yes: TIMESTAMPTZ in PostgreSQL, DATETIMEOFFSET in SQL Server, or UTC in a DATETIME column in MySQL. For calendar dates without a time (a birthday), use DATE.
Why does SQLite accept any type?
SQLite uses dynamic typing with “type affinity”: a column’s declared type is a preference, not a rule, and values keep their own type. Since version 3.37 you can declare a table STRICT to enforce INTEGER, REAL, TEXT, BLOB or ANY.
INT or BIGINT for primary keys?
INT runs out at about 2.1 billion rows (or inserts, since failed inserts can consume ids). If a table could plausibly grow that large, such as events, logs or messages, use BIGINT from the start; changing a primary key type later means rewriting the table and every foreign key.