Creating Tables

Building a Table

CREATE TABLE customers (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(150) NOT NULL UNIQUE,
  city       VARCHAR(80)  DEFAULT 'Unknown',
  created_at TIMESTAMPTZ  DEFAULT now()
);

What Each Piece Means

• SERIAL: auto-incrementing ID served by a sequence.
• NOT NULL: value required.
• UNIQUE: no duplicates in the column.
• DEFAULT: value when none supplied.
• TIMESTAMPTZ: timestamp with timezone; always prefer this over TIMESTAMP.

Constraints

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  total       NUMERIC(10,2) CHECK (total >= 0)
);

Foreign keys reference another table; CHECK enforces a condition on every row.

Altering Tables

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE customers DROP COLUMN phone;
ALTER TABLE customers RENAME COLUMN city TO hometown;

Key Points

• SERIAL provides auto-increment primary keys.
• Use NOT NULL, UNIQUE, DEFAULT and CHECK to protect data.
• REFERENCES creates a foreign key relationship.
• TIMESTAMPTZ stores timezone-aware timestamps safely.