Postgres Column Types: A Practical Guide

Postgres column types shape how your database stores, validates, indexes, and returns data. Choosing well at the start makes your schema easier to query and harder to misuse.

Text columns

Use text for most strings. Use varchar(n) when the length limit is a real business rule, not just a habit copied from another database.

Integer and numeric columns

• integer: good default for ordinary whole numbers.
• bigint: use for large counters, identifiers, or values that may grow beyond integer range.
• numeric: use for money-like values when exact decimal precision matters.

Dates and timestamps

Use date when you only need a calendar day. Use timestamptz for real moments in time, such as created-at and updated-at values. Avoid plain timestamp unless you intentionally do not want time zone handling.

UUIDs and identity columns

uuid works well for public identifiers and distributed systems. For simple internal primary keys, generated identity columns are usually cleaner than older serial patterns.

JSONB

Use jsonb for flexible structured data that does not deserve its own relational tables yet. Keep core fields as normal columns when you filter, join, validate, or index them regularly.

Arrays and booleans

Arrays are useful for small lists attached to one row, but a join table is better when the list needs relationships or rich querying. Booleans are best for clear true/false states; avoid turning multi-state workflows into several boolean columns.

Quick decision table