CREATE TABLE

Learning Focus

Use this lesson to understand CREATE TABLE in PostgreSQL with practical syntax, constraints, and expected outputs.

Concept Overview

CREATE TABLE defines a table's structure: columns, data types, defaults, and constraints.

Tables are where your data lives. Good table design prevents bad data and makes queries faster and simpler.

Why is it important?

• Data integrity: NOT NULL, CHECK, UNIQUE, and foreign keys enforce rules
• Maintainability: clear column names/types reduce application complexity
• Performance: correct keys and types enable better indexing and plans
• Modern identity: GENERATED ... AS IDENTITY replaces legacy serial

Where does it fit?

Schema lifecycle:

1. Create a database (CREATE DATABASE)
2. Create tables (CREATE TABLE)
3. Evolve them safely (ALTER TABLE)
4. Remove them (DROP TABLE)

---

Syntax & Rules

Core Syntax

CREATE TABLE table_name (
  column_name data_type [column_constraints...],
  ...
  [table_constraints...]
);

Common Patterns

Identity primary key + basic constraints:

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Schema-qualified name:

CREATE SCHEMA IF NOT EXISTS app;

CREATE TABLE app.orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(customer_id),
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);

Available Options / Parameters

Feature	Example	Meaning / Notes
IF NOT EXISTS	CREATE TABLE IF NOT EXISTS t (...);	avoids an error if table exists
Temporary table	CREATE TEMP TABLE t (...);	session-scoped; dropped at session end
Unlogged table	CREATE UNLOGGED TABLE t (...);	faster writes, less durable (not crash-safe)
Identity column	GENERATED ALWAYS AS IDENTITY	modern auto-increment
Defaults	DEFAULT now()	automatic values on insert
Column constraints	NOT NULL, UNIQUE, CHECK (...)	per-column enforcement
Table constraints	PRIMARY KEY (...), FOREIGN KEY (...)	multi-column enforcement

Key Rules and Considerations

• PostgreSQL folds unquoted identifiers to lowercase. Prefer lowercase/unquoted names.
• Use timestamptz for real-world timestamps.
• Prefer GENERATED ... AS IDENTITY over serial.
• Add constraints early; removing bad data later is expensive.

Inspect table structure in psql (meta-commands):

\d+ customers
\d+ app.orders

---

Step-by-Step Examples

Example 1: Create a Basic Table With an Identity Key (Beginner)

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Expected output:

DROP TABLE
CREATE TABLE

Explanation:

• Identity columns are explicit and standards-aligned.
• A UNIQUE constraint creates a unique index.

---

Example 2: Insert and Use RETURNING (Beginner)

INSERT INTO customers (email)
VALUES ('a@example.com'), ('b@example.com')
RETURNING customer_id, email;

Expected output (example):

 customer_id |     email
-------------+---------------
 1           | a@example.com
 2           | b@example.com
(2 rows)

Explanation:

• RETURNING lets you fetch generated values immediately.

---

Example 3: Create Related Tables With a Foreign Key (Intermediate)

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(customer_id),
  status      text NOT NULL,
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, status, amount) VALUES
  (1, 'paid', 10.00),
  (1, 'paid', 25.00),
  (2, 'failed', 5.00);

SELECT
  c.customer_id,
  c.email,
  COUNT(*) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.email
ORDER BY c.customer_id;

Expected output:

 customer_id |     email       | order_count
-------------+------------------+------------
 1           | a@example.com    | 2
 2           | b@example.com    | 1
(2 rows)

Explanation:

• Foreign keys enforce referential integrity.
• CHECK (amount >= 0) blocks negative values.

---

Example 4: Use CREATE TABLE IF NOT EXISTS (Intermediate)

CREATE TABLE IF NOT EXISTS customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Expected output:

NOTICE:  relation "customers" already exists, skipping
CREATE TABLE

---

Practical Use Cases

1) Enforce Business Rules With Constraints

Context: prevent invalid states at the database layer.

CREATE TABLE invoices (
  invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL CHECK (status IN ('draft', 'sent', 'paid', 'void')),
  total  numeric(12,2) NOT NULL CHECK (total >= 0)
);

2) Use Schemas for Organization

Context: separate app tables from admin tables.

CREATE SCHEMA IF NOT EXISTS app;
CREATE SCHEMA IF NOT EXISTS admin;

CREATE TABLE app.users (...);
CREATE TABLE admin.audit_log (...);

3) Speed Up Bulk Loads With UNLOGGED (Carefully)

Context: staging data you can regenerate.

CREATE UNLOGGED TABLE staging_import (
  raw_line text NOT NULL
);

---

Common Mistakes & Troubleshooting

Mistake 1: Using serial by Habit

What happens: it works, but it is an older pattern with implicit sequences.

Fix:

customer_id bigint GENERATED ALWAYS AS IDENTITY

---

Mistake 2: Forgetting Constraints

Wrong:

CREATE TABLE products (
  sku text,
  price numeric
);

What happens: NULL and invalid values slip in.

Fix:

CREATE TABLE products (
  sku   text PRIMARY KEY,
  price numeric(12,2) NOT NULL CHECK (price >= 0)
);

---

Mistake 3: Using timestamp When You Mean Real Time

What happens: timestamp has no timezone; apps can interpret it inconsistently.

Fix:

created_at timestamptz NOT NULL DEFAULT now()

Debugging tips:

1. Inspect definitions with \d+ table_name.
2. Use INSERT ... RETURNING to validate defaults/identity behavior.
3. If a foreign key fails, confirm referenced rows exist and types match.
4. Avoid quoted identifiers to prevent case-sensitivity surprises.

---

Best Practices

• ✅ Use identity columns for surrogate keys; ❌ rely on legacy serial for new designs.
• ✅ Add constraints at creation time; ❌ postpone integrity checks.
• ✅ Use timestamptz for event times; ❌ store time without timezone context.
• ✅ Keep naming consistent and unquoted; ❌ create case-sensitive names by quoting.
• ✅ Keep schema changes in migrations; ❌ hand-edit production schemas.

---

Hands-On Practice

Use this setup for the exercises:

DROP TABLE IF EXISTS practice_orders;
DROP TABLE IF EXISTS practice_customers;

CREATE TABLE practice_customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE
);

Exercise 1 (Easy): Create a related table

Task: Create practice_orders with an identity primary key and a foreign key to practice_customers.

-- Your SQL here

Solution:

CREATE TABLE practice_orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES practice_customers(customer_id)
);

---

Exercise 2 (Medium): Add data checks

Task: Add amount numeric(12,2) NOT NULL CHECK (amount >= 0) to practice_orders.

-- Your SQL here

Solution:

ALTER TABLE practice_orders
ADD COLUMN amount numeric(12,2) NOT NULL CHECK (amount >= 0);

---

Exercise 3 (Advanced): Insert with RETURNING

Task: Insert one customer and return the generated customer_id.

-- Your SQL here

Solution:

INSERT INTO practice_customers (email)
VALUES ('practice@example.com')
RETURNING customer_id;

---

Connection to Other Concepts

Concept	Why it matters
Data types	correct types prevent bugs and improve plans
Constraints	enforce business rules at the database layer
Indexes	primary keys and unique constraints create indexes
ALTER TABLE	schema evolution after creation
Transactions	coordinate changes safely

---

Visual Learning Diagram

flowchart TD
  A[Choose Types] --> B[Add Constraints]
  B --> C[CREATE TABLE]
  C --> D[Load Data]
  D --> E[Query + Index]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E allNodes
  class C highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Missing constraints	bad data	add NOT NULL, CHECK, FK early
No primary key	hard updates/replication	add a PK or strong natural key
Quoted identifiers	awkward SQL forever	keep names unquoted and lowercase
Wrong timestamp type	timezone bugs	prefer timestamptz
Overusing JSON for stable fields	weak constraints	model stable fields as columns

---

Quick Reference

CREATE TABLE t (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ...);
CREATE TABLE IF NOT EXISTS t (...);
CREATE TEMP TABLE t (...);
CREATE UNLOGGED TABLE t (...);
INSERT INTO t (...) VALUES (...) RETURNING *;

What's Next

• Previous: DROP DATABASE - Safely remove databases and handle active sessions.
• Next: DROP TABLE - Remove tables and understand dependency behavior.
• Module Overview - Return to the Database Management index.