CREATE, ALTER & DROP

Build and evolve tables over time.

Building and changing tables

CREATE TABLE defines a table; DROP TABLE removes it. Schemas evolve, so ALTER TABLE is everyday work:

ALTER TABLE t ADD COLUMN c int DEFAULT 0;
ALTER TABLE t DROP COLUMN c;
ALTER TABLE t ADD CONSTRAINT ... ; / RENAME COLUMN ...

For auto-incrementing keys, serial (or the SQL-standard GENERATED ALWAYS AS IDENTITY) hands out values from a sequence so you don't supply the id:

CREATE TABLE note (id serial PRIMARY KEY, body text NOT NULL);
INSERT INTO note (body) VALUES ('first') RETURNING id;  -- id = 1

ALTER TABLE rewrites the shape, not your data: shelf keeps whatever rows it had, and every row — existing or new — reads 100 for capacity because that is what the DEFAULT says. Schemas are expected to change; this is the everyday move.