Data Types

-- ===== Numeric =====
SMALLINT       -- 2 bytes, -32k..32k
INTEGER / INT  -- 4 bytes, ~-2.1B..2.1B
BIGINT         -- 8 bytes
NUMERIC(p, s)  -- exact decimal, use for money: NUMERIC(12, 2)
REAL           -- 4-byte float
DOUBLE PRECISION -- 8-byte float

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,    -- 8-byte auto-increment
  total      NUMERIC(12, 2) NOT NULL,
  units      INTEGER NOT NULL
);

-- ===== Text =====
TEXT       -- unlimited; this is the right default
VARCHAR(n) -- arbitrary limit; rarely earns the maintenance cost
CHAR(n)    -- fixed-length, pads with spaces; almost never the right tool

-- All three have the same performance in Postgres; pick TEXT and add CHECKs for length.

ALTER TABLE orders ADD COLUMN note TEXT CHECK (char_length(note) <= 500);

-- ===== Booleans =====
BOOLEAN  -- TRUE / FALSE / NULL

-- ===== Date / time =====
DATE                    -- 4 bytes
TIMESTAMP               -- 8 bytes, NO timezone (rarely the right choice)
TIMESTAMPTZ             -- 8 bytes, with tz (always store this)
INTERVAL                -- '1 day', '2 hours 30 minutes'
TIME                    -- time of day, no date

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  duration    INTERVAL
);

-- Pattern: store in TIMESTAMPTZ, format/convert at the edge.

-- ===== UUID =====
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL
);

-- ===== JSON =====
JSON   -- raw text; preserves order + whitespace
JSONB  -- decomposed binary; indexed; use this by default

CREATE TABLE profiles (
  user_id UUID PRIMARY KEY,
  data    JSONB NOT NULL
);

-- Index a specific path:
CREATE INDEX profiles_data_country ON profiles ((data->>'country'));

-- Query:
SELECT user_id FROM profiles WHERE data @> '{"country": "AU"}';
SELECT data->>'email' FROM profiles WHERE data ? 'verified';

-- ===== Arrays =====
CREATE TABLE posts (
  id   BIGSERIAL PRIMARY KEY,
  tags TEXT[] NOT NULL DEFAULT '{}'
);

SELECT id FROM posts WHERE 'sql' = ANY(tags);
CREATE INDEX posts_tags_gin ON posts USING GIN (tags);

-- ===== Enum =====
CREATE TYPE order_status AS ENUM ('new', 'paid', 'shipped', 'cancelled');
ALTER TABLE orders ADD COLUMN status order_status NOT NULL DEFAULT 'new';

-- Enum values are ordered as defined; add new values with ALTER TYPE ... ADD VALUE.

-- ===== Network types =====
INET    -- IPv4 or IPv6 address
CIDR    -- network address
MACADDR -- 6-byte hardware address

CREATE TABLE logins (
  user_id UUID NOT NULL,
  ip      INET NOT NULL,
  at      TIMESTAMPTZ NOT NULL
);

-- ===== Generated / computed =====
ALTER TABLE orders
  ADD COLUMN total_cents BIGINT GENERATED ALWAYS AS ((total * 100)::bigint) STORED;

-- ===== Patterns to internalise =====
-- - TEXT for strings; CHECK for length when you need it
-- - TIMESTAMPTZ everywhere; never TIMESTAMP without tz
-- - NUMERIC for money; never DOUBLE PRECISION
-- - JSONB > JSON; index frequently-queried paths
-- - UUID PKs with gen_random_uuid() for distributed-friendly ids
-- - Enums for closed sets; reach for a lookup table when membership changes often

-- ===== Pitfalls =====
-- - DOUBLE PRECISION for money -> rounding errors in totals
-- - TIMESTAMP WITHOUT TIME ZONE -> daylight-saving + tz-conversion bugs forever
-- - VARCHAR(n) with arbitrary n -> migrations every time someone enters longer text
-- - JSONB without an index -> sequential scans on every search
-- - Enums with values you outgrow -> alter-add is fine, alter-rename is risky

-- numeric: int, bigint, numeric(10,2), real, double precision
-- text:    text, varchar(n), char(n)
-- time:    date, time, timestamp, timestamptz, interval
-- bool:    boolean
-- json:    json, jsonb
-- other:   uuid, bytea, inet, ARRAY