PostgreSQL Data Types

What are Data Types?

Every column in a PostgreSQL table has a data type that determines what kind of values it can store. Choosing the right type ensures data integrity and optimal performance.

Common Data Types

Numeric

• INTEGER / INT — Whole numbers (-2 billion to 2 billion)
• BIGINT — Large whole numbers
• SERIAL — Auto-incrementing integer
• NUMERIC(p, s) / DECIMAL — Exact decimal numbers
• REAL / DOUBLE PRECISION — Floating-point numbers

Text

• VARCHAR(n) — Variable-length string up to n characters
• CHAR(n) — Fixed-length string
• TEXT — Unlimited-length string

Boolean

• BOOLEAN — true, false, or NULL

Date and Time

• DATE — Calendar date
• TIME — Time of day
• TIMESTAMP — Date and time
• TIMESTAMPTZ — Timestamp with time zone

Other

• JSONB — Binary JSON data
• UUID — Universally unique identifier
• ARRAY — Array of any type

Key Points

1. Choose the Smallest Type — Use INTEGER instead of BIGINT unless you need the range.
2. TEXT vs VARCHAR — In PostgreSQL, TEXT and VARCHAR without a length perform identically.
3. NUMERIC for Money — Use NUMERIC for financial calculations; floating-point types can introduce rounding errors.
4. TIMESTAMPTZ — Always prefer TIMESTAMPTZ over TIMESTAMP for time-zone-aware applications.
5. JSONB Over JSON — JSONB is more efficient than JSON for storage and querying.