DDL: Creating and Changing Tables

DDL (Data Definition Language) creates and changes the structure that rows live in. Every example below runs in order against one scratch database.

CREATE TABLE With Constraints

Constraints are rules the database enforces on every write.

-- create.sql
CREATE TABLE products (
    id        INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku       TEXT NOT NULL UNIQUE,
    name      TEXT NOT NULL,
    price     NUMERIC(8, 2) NOT NULL CHECK (price > 0),
    in_stock  BOOLEAN NOT NULL DEFAULT true
);
\d products

$ psql -d sqldemo -f create.sql
CREATE TABLE
                            Table "public.products"
  Column  |     Type     | Collation | Nullable |           Default            
----------+--------------+-----------+----------+------------------------------
 id       | integer      |           | not null | generated always as identity
 sku      | text         |           | not null | 
 name     | text         |           | not null | 
 price    | numeric(8,2) |           | not null | 
 in_stock | boolean      |           | not null | true
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
    "products_sku_key" UNIQUE CONSTRAINT, btree (sku)
Check constraints:
    "products_price_check" CHECK (price > 0::numeric)

\d products is a psql command that describes the table. It shows each constraint turned into something concrete: the primary key and UNIQUE became indexes, and CHECK is listed by the name PostgreSQL generated for it. GENERATED ALWAYS AS IDENTITY is the standard-SQL replacement for SERIAL.

Constraints Reject Bad Rows

Each insert below breaks a different rule.

-- violations.sql
INSERT INTO products (sku, name, price) VALUES ('A-1', 'Lamp', 25.00);
INSERT INTO products (sku, name, price) VALUES ('A-1', 'Desk', 90.00);
INSERT INTO products (sku, name, price) VALUES ('B-2', 'Chair', -5);
INSERT INTO products (id, sku, name, price) VALUES (99, 'C-3', 'Rug', 40.00);

$ psql -d sqldemo -f violations.sql
INSERT 0 1
psql:violations.sql:3: ERROR:  duplicate key value violates unique constraint "products_sku_key"
DETAIL:  Key (sku)=(A-1) already exists.
psql:violations.sql:4: ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (3, B-2, Chair, -5.00, t).
psql:violations.sql:5: ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Only the lamp was stored. The duplicate SKU hit the unique index, the negative price hit the CHECK, and the last insert tried to choose its own id, which GENERATED ALWAYS forbids. The error names the constraint, which is why it pays to give important constraints readable names.

ALTER TABLE

ALTER TABLE adds, renames, and retypes columns on a live table.

-- alter.sql
ALTER TABLE products ADD COLUMN category TEXT;
ALTER TABLE products ALTER COLUMN category SET NOT NULL;
UPDATE products SET category = 'home' WHERE category IS NULL;
ALTER TABLE products ALTER COLUMN category SET NOT NULL;
ALTER TABLE products RENAME COLUMN in_stock TO available;
SELECT id, sku, category, available FROM products;

$ psql -d sqldemo -f alter.sql
ALTER TABLE
psql:alter.sql:3: ERROR:  column "category" of relation "products" contains null values
UPDATE 1
ALTER TABLE
ALTER TABLE
 id | sku | category | available 
----+-----+----------+-----------
  1 | A-1 | home     | t
(1 row)

The first SET NOT NULL failed because the lamp’s new category was NULL, and PostgreSQL checks existing rows before accepting a constraint. Backfilling with UPDATE first made the second attempt succeed. That order, add nullable, backfill, then constrain, is the safe pattern for real migrations.

DROP TABLE and Dependencies

A table that other objects depend on cannot be dropped quietly.

-- drop.sql
CREATE TABLE reviews (
    id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id),
    stars      INT CHECK (stars BETWEEN 1 AND 5)
);
DROP TABLE products;
DROP TABLE products CASCADE;
\d reviews

$ psql -d sqldemo -f drop.sql
CREATE TABLE
psql:drop.sql:7: ERROR:  cannot drop table products because other objects depend on it
DETAIL:  constraint reviews_product_id_fkey on table reviews depends on table products
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
psql:drop.sql:8: NOTICE:  drop cascades to constraint reviews_product_id_fkey on table reviews
DROP TABLE
                           Table "public.reviews"
   Column   |  Type   | Collation | Nullable |           Default            
------------+---------+-----------+----------+------------------------------
 id         | integer |           | not null | generated always as identity
 product_id | integer |           | not null | 
 stars      | integer |           |          | 
Indexes:
    "reviews_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "reviews_stars_check" CHECK (stars >= 1 AND stars <= 5)

The plain DROP refused and named the dependent foreign key. CASCADE dropped products and removed only the foreign key constraint from reviews: the reviews table and its column survive, now pointing at nothing. Read the NOTICE lines before you trust a cascade.

What Happens When a Referenced Row Is Deleted

A foreign key can say what to do with child rows when their parent row is deleted: ON DELETE CASCADE, SET NULL or RESTRICT. The default, NO ACTION, refuses the delete like RESTRICT does.

-- fk_actions.sql
CREATE TABLE teams (id INT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE members (id INT PRIMARY KEY, name TEXT,
    team_id INT REFERENCES teams(id) ON DELETE CASCADE);
CREATE TABLE tickets (id INT PRIMARY KEY, title TEXT,
    team_id INT REFERENCES teams(id) ON DELETE SET NULL);
CREATE TABLE budgets (team_id INT PRIMARY KEY REFERENCES teams(id) ON DELETE RESTRICT,
    amount INT);
INSERT INTO teams VALUES (1, 'web'), (2, 'data');
INSERT INTO members VALUES (10, 'Ana', 1), (11, 'Ben', 1), (12, 'Cai', 2);
INSERT INTO tickets VALUES (100, 'Fix login', 1), (101, 'New report', 2);
INSERT INTO budgets VALUES (1, 5000);
DELETE FROM teams WHERE id = 1;
DELETE FROM budgets WHERE team_id = 1;
DELETE FROM teams WHERE id = 1;
SELECT * FROM members ORDER BY id;
SELECT * FROM tickets ORDER BY id;

$ psql -d sqldemo -f fk_actions.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
INSERT 0 2
INSERT 0 1
psql:fk_actions.sql:13: ERROR:  update or delete on table "teams" violates foreign key constraint "budgets_team_id_fkey" on table "budgets"
DETAIL:  Key (id)=(1) is still referenced from table "budgets".
DELETE 1
DELETE 1
 id | name | team_id 
----+------+---------
 12 | Cai  |       2
(1 row)

 id  |   title    | team_id 
-----+------------+---------
 100 | Fix login  |        
 101 | New report |       2
(2 rows)

The first DELETE failed because of budgets, the RESTRICT table: team 1 still has a budget row, so it can’t be deleted, and nothing changed. Once that budget row is gone, the same DELETE succeeds and each foreign key applies its own rule. CASCADE deleted Ana and Ben with their team, so only Cai is left in members. SET NULL kept the “Fix login” ticket but cleared its team_id, shown as the empty value. CASCADE is convenient but can delete far more than you expect through a chain of tables, so it suits rows that can’t exist without their parent, like the lines of an order.

TRUNCATE

TRUNCATE empties a table in one step; RESTART IDENTITY also resets its id counter.

-- truncate.sql
INSERT INTO reviews (product_id, stars) VALUES (1, 5), (1, 4);
TRUNCATE reviews;
INSERT INTO reviews (product_id, stars) VALUES (1, 3);
SELECT id, stars FROM reviews;
TRUNCATE reviews RESTART IDENTITY;
INSERT INTO reviews (product_id, stars) VALUES (1, 2);
SELECT id, stars FROM reviews;

$ psql -d sqldemo -f truncate.sql
INSERT 0 2
TRUNCATE TABLE
INSERT 0 1
 id | stars 
----+-------
  3 |     3
(1 row)

TRUNCATE TABLE
INSERT 0 1
 id | stars 
----+-------
  1 |     2
(1 row)

After the first TRUNCATE, the identity kept counting, so the review with 3 stars got id 3. After RESTART IDENTITY, the next row got id 1 again. TRUNCATE does not scan the rows, so on a large table it is far faster than DELETE FROM reviews.

Schemas

A schema is a namespace inside a database; search_path decides which schemas unqualified names resolve against.

-- schemas.sql
CREATE SCHEMA billing;
CREATE TABLE billing.invoices (id INT PRIMARY KEY, total NUMERIC(10, 2));
INSERT INTO billing.invoices VALUES (1, 99.50);
SELECT * FROM invoices;
SET search_path TO billing, public;
SELECT * FROM invoices;

$ psql -d sqldemo -f schemas.sql
CREATE SCHEMA
CREATE TABLE
INSERT 0 1
psql:schemas.sql:5: ERROR:  relation "invoices" does not exist
LINE 1: SELECT * FROM invoices;
                      ^
SET
 id | total 
----+-------
  1 | 99.50
(1 row)

The first SELECT failed because only public was on the search path. After adding billing, the unqualified name resolved. Qualified names like billing.invoices always work regardless of the path.

Generated Columns

A generated column is computed from other columns on every write.

-- generated.sql
CREATE TABLE lines (
    qty        INT NOT NULL,
    unit_price NUMERIC(8, 2) NOT NULL,
    total      NUMERIC(10, 2) GENERATED ALWAYS AS (qty * unit_price) STORED
);
INSERT INTO lines (qty, unit_price) VALUES (3, 2.50);
UPDATE lines SET qty = 4;
SELECT * FROM lines;
INSERT INTO lines VALUES (1, 1.00, 999);

$ psql -d sqldemo -f generated.sql
CREATE TABLE
INSERT 0 1
UPDATE 1
 qty | unit_price | total 
-----+------------+-------
   4 |       2.50 | 10.00
(1 row)

psql:generated.sql:10: ERROR:  cannot insert a non-DEFAULT value into column "total"
DETAIL:  Column "total" is a generated column.

total followed the update to 4 × 2.50 without anyone touching it, and a direct write to it is refused. The value can never disagree with its inputs, which is the point.

Practice

1. Create a customers table with an identity id, a required unique email, and a created_at that defaults to the current time. Describe it with \d.

-- practice_customers.sql
SET search_path TO public;
CREATE TABLE customers (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
\d customers

$ psql -d sqldemo -f practice_customers.sql
SET
CREATE TABLE
                                  Table "public.customers"
   Column   |           Type           | Collation | Nullable |           Default            
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 email      | text                     |           | not null | 
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

2. Add a CHECK constraint named email_has_at requiring an @ in email, then try to insert 'nobody'.

-- practice_check.sql
ALTER TABLE customers ADD CONSTRAINT email_has_at CHECK (email LIKE '%@%');
INSERT INTO customers (email) VALUES ('nobody');

$ psql -d sqldemo -f practice_check.sql
ALTER TABLE
psql:practice_check.sql:3: ERROR:  new row for relation "customers" violates check constraint "email_has_at"
DETAIL:  Failing row contains (1, nobody, 2026-09-24 16:59:26.82716+00).

3. Add a generated domain column to customers holding everything after the @.

-- practice_domain.sql
ALTER TABLE customers
    ADD COLUMN domain TEXT GENERATED ALWAYS AS (split_part(email, '@', 2)) STORED;
INSERT INTO customers (email) VALUES ('ana@example.com');
SELECT email, domain FROM customers;

$ psql -d sqldemo -f practice_domain.sql
ALTER TABLE
INSERT 0 1
      email      |   domain    
-----------------+-------------
 ana@example.com | example.com
(1 row)