SQL Data Types Table
| Type | Category | Size / Range |
|---|---|---|
| SMALLINT | Numeric | 2 bytes, -32,768 to +32,767 |
| INTEGER | Numeric | 4 bytes, -2.1B to +2.1B |
| BIGINT | Numeric | 8 bytes, -9.2Q to +9.2Q |
| DECIMAL(p,s) | Numeric | Variable, exact precision - use for money |
| REAL | Numeric | 4 bytes, 6 decimal digits precision |
| DOUBLE PRECISION | Numeric | 8 bytes, 15 decimal digits precision |
| VARCHAR(n) | Text | Variable length, max n characters |
| TEXT | Text | Variable length, unlimited (PostgreSQL preference) |
| CHAR(n) | Text | Fixed length, blank-padded |
| BOOLEAN | Boolean | 1 byte, true or false |
| DATE | Date/Time | 4 bytes, calendar date only (no time) |
| TIME | Date/Time | Time of day only (no date) |
| TIMESTAMP | Date/Time | 8 bytes, date and time (no timezone) |
| TIMESTAMPTZ | Date/Time | 8 bytes, date and time with timezone |
| UUID | Other | 16 bytes, universally unique identifier |
| JSONB | Other | Variable, binary JSON - indexed and queryable |
| BYTEA | Other | Variable, binary data (files, images) |
| SERIAL | Other | Auto-incrementing integer (4 bytes) |
Choosing the right SQL data type is a one-time decision that affects storage, indexing, query performance and correctness forever - changing a column type on a million-row table requires a full table rewrite. This table covers the 18 most-used PostgreSQL types verbatim from the official documentation.
The three PostgreSQL community preferences that surprise newcomers: TEXT over VARCHAR (identical performance, no arbitrary limit), TIMESTAMPTZ over TIMESTAMP (always stores UTC, converts on display), and JSONB over JSON (binary format, indexed, queryable).
How to use
- Filter by type name or category - numeric, text, date, other - to find the type you need.
- Check the size column for storage planning: BIGINT is 8 bytes, TEXT is variable, UUID is fixed at 16 bytes.
- Click any type to copy the type name for a CREATE TABLE statement.
Frequently asked questions
What is the difference between VARCHAR(n) and TEXT?
In PostgreSQL they are functionally identical - both store variable-length text with no performance difference. VARCHAR(n) adds a length check, but the PostgreSQL community prefers TEXT because you can add a CHECK constraint (char_length(name) <= 255) that is easier to change later. Other databases (MySQL) treat these very differently.
Why should I use DECIMAL instead of REAL for money?
REAL and DOUBLE PRECISION are floating-point types that cannot represent decimal fractions exactly: 0.1 + 0.2 = 0.30000000000000004. DECIMAL uses exact decimal arithmetic, so 0.1 + 0.2 = 0.3. For any application that handles money, the difference between $10.00 and $10.000000000000002 is a real bug.
What is the difference between TIMESTAMP and TIMESTAMPTZ?
TIMESTAMP stores the literal date and time with no timezone information - 2026-01-01 12:00 means whatever timezone the reader assumes. TIMESTAMPTZ stores the same value but converts input to UTC and converts output to the session timezone - making it the only safe choice for applications with users in multiple timezones.
When should I use JSONB instead of separate columns?
Use JSONB when the data structure varies per row (product attributes with different fields) or when you need flexibility without schema migrations. Use separate columns when the fields are always present, need foreign key constraints, or require strict type enforcement. JSONB supports GIN indexing for fast queries on nested keys.