SQL data types every developer must know

Type Choice Affects Storage, Speed, and Correctness

Picking the wrong type causes subtle bugs. Storing a price as FLOAT introduces rounding errors — 0.1 + 0.2 is not exactly 0.3 in floating point. Use NUMERIC(10,2) for money.

The Types You'll Use Most

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,        -- auto-increment integer
  sku         VARCHAR(50) NOT NULL,      -- bounded string
  description TEXT,                      -- unbounded string
  price       NUMERIC(10, 2) NOT NULL,   -- exact decimal
  in_stock    BOOLEAN DEFAULT true,
  created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

VARCHAR vs TEXT: In PostgreSQL there is no performance difference. Use VARCHAR when you want to enforce a max length as a data rule. Use TEXT when length is unconstrained.

SERIAL vs BIGSERIAL: SERIAL is a 32-bit integer — fine for most tables. BIGSERIAL is 64-bit — use it when a table could exceed 2 billion rows (event logs, analytics).

Timestamps and Time Zones

Always use TIMESTAMP WITH TIME ZONE (also written TIMESTAMPTZ). It stores in UTC and converts to the session time zone on read. TIMESTAMP without time zone stores whatever you give it and makes time comparisons across zones unreliable.

-- Cast a string to a date type explicitly
SELECT '2024-01-15'::DATE + INTERVAL '30 days';