Data Types & JSONB

Common Data Types

TEXT, VARCHAR(n)     -- strings
INTEGER, BIGINT      -- whole numbers
NUMERIC(10,2)        -- exact decimals
BOOLEAN              -- true/false
TIMESTAMP, DATE      -- time
UUID                 -- unique identifiers
JSONB                -- structured JSON

JSON vs JSONB

JSONB stores JSON in a binary format, enabling indexing and faster queries. Prefer JSONB for most use cases.

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

SELECT payload->>'type' AS type
FROM events
WHERE payload->'meta'->>'source' = 'web';

CREATE INDEX events_payload_gin
ON events USING GIN (payload);