Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.
Quick answer
SQL data types specify the kind of data a column can store, its physical memory/disk footprint, and the mathematical or comparison operators it supports. The core data type families include: exact numerics (INT, BIGINT, NUMERIC/DECIMAL), approximate numerics (REAL, FLOAT), character strings (VARCHAR, TEXT), temporal types (DATE, TIMESTAMP WITH TIME ZONE), and semi-structured types (JSONB).
FAQs
Why should currency and financial data never use FLOAT or DOUBLE?
FLOAT and DOUBLE are approximate floating-point types governed by IEEE 754. They introduce binary rounding errors (e.g., 0.1 + 0.2 = 0.30000000000000004). Always use exact fixed-point `NUMERIC(precision, scale)` or `DECIMAL` for monetary values.
What is the difference between TIMESTAMP WITH TIME ZONE and WITHOUT TIME ZONE in PostgreSQL?
`TIMESTAMP WITH TIME ZONE` (timestamptz) converts incoming timestamp strings to UTC on storage and converts UTC back to the client's session timezone on read. `WITHOUT TIME ZONE` stores the raw numbers without timezone awareness, leading to critical ambiguity during daylight savings and server migrations.
Does VARCHAR(255) save disk space compared to TEXT in PostgreSQL?
No. In PostgreSQL, VARCHAR(n) and TEXT use the exact same underlying storage mechanism (varlena header + compressed data). VARCHAR(255) merely enforces a length check constraint; it does not allocate 255 fixed bytes.
Why is utf8mb4 mandatory for MySQL character columns?
MySQL's legacy `utf8` character set only supports 3 bytes per character, failing on 4-byte characters (emojis, mathematical symbols, and diverse international scripts). Always declare `CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci`.
Choosing the correct SQL data type is a fundamental architectural decision. The data type dictates storage compactness, index memory efficiency, validation rules, and whether arithmetic operations suffer from silent rounding corruption.
Key takeaways
Always use NUMERIC/DECIMAL for financial balances; never use FLOAT or DOUBLE.
Default to TIMESTAMP WITH TIME ZONE (timestamptz) stored in UTC for all event logs and audit timestamps.
In MySQL, always use utf8mb4 to avoid silent truncation on emojis and international characters.
Choose BIGINT over INT for auto-incrementing primary keys to prevent sequence integer overflow.
Cross-dialect data type comparison matrix
Understanding how canonical data types map across different database engines prevents schema migration bugs:
Exact Integer (64-bit): PostgreSQL BIGINT | MySQL BIGINT | SQL Server BIGINT | SQLite INTEGER
Exact Currency: PostgreSQL NUMERIC(12,2) | MySQL DECIMAL(12,2) | SQL Server DECIMAL(12,2) | BigQuery NUMERIC
Variable Text: PostgreSQL TEXT / VARCHAR | MySQL VARCHAR(n) utf8mb4 | SQL Server NVARCHAR(n) | SQLite TEXT
UTC Timestamp: PostgreSQL TIMESTAMPTZ | MySQL TIMESTAMP / DATETIME | SQL Server DATETIMEOFFSET | BigQuery TIMESTAMP
Binary Document: PostgreSQL JSONB | MySQL JSON | SQL Server JSON / NVARCHAR | BigQuery JSON
Robust schema definition using optimal data types
CREATE TABLE billing.invoices ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, invoice_uuid UUID NOT NULL DEFAULT gen_random_uuid(), customer_id BIGINT NOT NULL, -- Exact fixed-point numeric: 12 total digits, 2 decimal places subtotal_amount NUMERIC(12, 2) NOT NULL CHECK (subtotal_amount >= 0), tax_amount NUMERIC(12, 2) NOT NULL DEFAULT 0.00, -- Timezone-aware timestamp normalized to UTC issued_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), paid_at TIMESTAMPTZ, -- JSONB for unstructured line-item metadata metadata JSONB NOT NULL DEFAULT '{}'::jsonb);