Creating and altering tables

Before you can store data you need a table, and real schemas change over time.

CREATE TABLE names a table and lists its columns, each with a type and optional constraints:

CREATE TABLE products (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     text          NOT NULL,
    category text          NOT NULL DEFAULT 'hardware',
    price    numeric(10,2) NOT NULL
);

A few pieces to recognise:

NOT NULL forbids missing values. Make it your default and allow NULL only when "unknown" is a real state.
DEFAULT supplies a value when an INSERT leaves the column out.
PRIMARY KEY marks the identifying column; GENERATED ALWAYS AS IDENTITY makes the database number rows automatically. The keys lesson covers both.

Names: use lower-case snake_case. Unquoted names are case-insensitive in PostgreSQL, but a table created as "OrderItems" must be quoted with exactly that capitalisation forever after.

Schemas evolve, and ALTER TABLE changes a table in place: add or drop a column, rename one, change a type, add a constraint. Adding a nullable column, or one with a constant default, is a fast metadata-only change in PostgreSQL 11 and later, but changing a column's type usually rewrites the whole table while holding an exclusive lock. On a large production table that matters.

DROP TABLE removes a table and its data permanently. IF EXISTS / IF NOT EXISTS make scripts re-runnable.

One PostgreSQL strength: DDL is transactional. You can run CREATE and ALTER statements inside BEGIN ... COMMIT and roll them all back on failure. MySQL and Oracle commit implicitly on most DDL, so a half-applied migration stays half-applied.

Example

CREATE TABLE IF NOT EXISTS customers (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    email      text NOT NULL,
    city       text,                         -- NULL allowed: may be unknown
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Evolve the schema
ALTER TABLE customers ADD COLUMN phone text;
ALTER TABLE customers RENAME COLUMN phone TO phone_number;
ALTER TABLE customers ALTER COLUMN city SET DEFAULT 'unknown';
ALTER TABLE customers DROP COLUMN phone_number;

-- Inspect it in psql:  \d customers

DROP TABLE IF EXISTS scratch_import;

Remember

CREATE TABLE lists columns with types, NOT NULL, DEFAULT and keys
ALTER TABLE changes a live table; some changes rewrite and lock it
PostgreSQL DDL is transactional; MySQL and Oracle DDL commits implicitly

Watch out for

Creating mixed-case quoted names like "CustomerId" and then having to quote them in every query.

Check yourself

A table was created with CREATE TABLE "OrderItems" (...). Which query works in PostgreSQL?
What happens to a column omitted from an INSERT when it has DEFAULT now()?

Official docs: www.postgresql.org/docs/current/sql-createtable.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks