SQL Basics

With a database and schema in place from Architecture, this covers the SQL you'll use constantly: defining tables and moving rows in and out of them.

Creating structure

CREATE TABLE users (
id         serial PRIMARY KEY,
email      text NOT NULL UNIQUE,
full_name  text,
created_at timestamptz NOT NULL DEFAULT now()
);

A few constraints worth knowing by default:

• serial allocates an auto-incrementing integer backed by a sequence — the modern equivalent, preferred in new schemas, is GENERATED ALWAYS AS IDENTITY.
• NOT NULL and UNIQUE are enforced on every write, not advisory.
• DEFAULT now() fills the column automatically if the INSERT doesn't specify it.

CREATE TABLE users (
id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email      text NOT NULL UNIQUE,
full_name  text,
created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE and DROP TABLE work as you'd expect — Postgres will enforce constraints on ALTER TABLE ... ADD CONSTRAINT against existing data, and DROP TABLE takes an ACCESS EXCLUSIVE lock (see Locks & Deadlocks).

Moving rows

INSERT INTO users (email, full_name) VALUES
('ada@example.com', 'Ada Lovelace'),
('alan@example.com', 'Alan Turing');

UPDATE users SET full_name = 'A. Lovelace' WHERE email = 'ada@example.com';

DELETE FROM users WHERE email = 'alan@example.com';

SELECT id, email, full_name FROM users WHERE created_at > now() - interval '1 day';

UPDATE and DELETE here are ordinary, fast, row-targeted operations — not batch rewrites.

Next: Data Types — picking the right column types for what you're storing.