PostgreSQL's richer features

📘What it really is

Convert your SQLite schema to proper PostgreSQL types and features:

• Ids: id uuid PRIMARY KEY DEFAULT gen_random_uuid() or id bigint GENERATED ALWAYS AS IDENTITY.

• Times: created_at timestamptz NOT NULL DEFAULT now(). Date functions: date_trunc('month', placed_at), now() - interval '30 days'.

• Money: numeric(12, 2) or bigint paise.

• Fixed lists: a CHECK (status IN (...)) constraint, or an enum type: CREATE TYPE enquiry_status AS ENUM ('new', 'in-progress', 'closed');.

• Case-insensitive uniqueness: CREATE UNIQUE INDEX ON customers (lower(email));.

• Flexible data: jsonb columns, queried with operators (details->>'source') and indexable with GIN indexes. Use sparingly (Module 51, Lesson 4).

• Full-text search: search by words, with ranking, in many languages:

SELECT name FROM customers
WHERE to_tsvector('simple', name || ' ' || coalesce(notes, '')) @@ plainto_tsquery('simple', 'kumari patna');

(A stored generated column with a GIN index makes it fast.)

• Upsert: insert, or update if it already exists: INSERT ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;.

• Row locking: SELECT ... FOR UPDATE inside a transaction (Module 57, Lesson 2).

• Partial indexes: index only what you query: CREATE INDEX ON enquiries (created_at) WHERE status = 'new';.