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.

Enums, checks, and lookup tables

Postgres gives you several ways to model a small set of allowed values. Use an enum when the list is stable and belongs in the database type system. Use a CHECK constraint when you want a lightweight rule on a text column. Use a lookup table when the values need labels, ordering, permissions, or frequent edits.

create table orders (
  id generated always as identity primary key,
  status text not null check (status in ('draft', 'paid', 'shipped')),
  total numeric(12, 2) not null,
  created_at timestamptz not null default now()
);

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

Rules of thumb

• Prefer text until a length limit is a real product rule.
• Use timestamptz for events that happened at a specific moment.
• Use numeric for exact decimal math and double precision for approximate scientific values.
• Keep frequently queried JSON fields as normal typed columns.
• Prefer generated identity columns over older serial defaults for new schemas.