CREATE TABLE in PostgreSQL

Postgres DDL resembles ANSI SQL with dialect additions: tablespaces, inheritance (legacy), and rich constraints. Always pair CREATE with explicit types and defaults.

Basic table

CREATE TABLE products (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Practice: Apply DDL in a throwaway practice database. Use \d table in psql to verify constraints and indexes.

Foreign keys

CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  qty INT NOT NULL CHECK (qty > 0)
);

Alter safely

ALTER TABLE products ADD COLUMN archived BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE products ALTER COLUMN name SET NOT NULL;

Production changes go through reviewed migrations—see Django migrations for app-driven workflows.

Important interview questions and answers

1. Q: GENERATED IDENTITY vs SERIAL?
   A: IDENTITY is standard SQL; SERIAL is legacy shorthand still common in tutorials.
2. Q: ON DELETE CASCADE risk?
   A: Deleting parent removes children—verify business rules before enabling.

Self-check

1. How do you add a NOT NULL column to a populated table safely?
2. What does REFERENCES enforce?

Tip: Align Django migrations with the same engine you use in production.