PostgreSQL

Everything in the core works in PostgreSQL — it's one of the most standards- compliant databases with strong ACID guarantees. This appendix covers the conveniences and extensions you'll reach for once you target Postgres specifically. (These examples aren't runnable here, since the in-browser engine is SQLite.)

Auto-increment columns

Postgres generates ids with SERIAL (older) or the SQL-standard GENERATED ... AS IDENTITY (preferred):

CREATE TABLE customers (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

RETURNING

Get values back from a write in one round trip — great for the new id:

INSERT INTO customers (name) VALUES ('Ada') RETURNING id;
UPDATE products SET price = price * 1.1 WHERE id = 3 RETURNING name, price;

Upsert: ON CONFLICT

Insert, or update if the row already exists — atomically:

INSERT INTO inventory (product_id, qty) VALUES (3, 10)
ON CONFLICT (product_id) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;

Case-insensitive matching and rich types

• ILIKE is a case-insensitive LIKE: name ILIKE 'a%'.
• Postgres has powerful native types: arrays, JSONB (indexed JSON), ranges, full-text search, and more — well beyond the standard.

Cheat-sheet

Where to go next

The other appendices cover SQLite (the engine these exercises run on) and SQL Server (T-SQL).