CREATE TABLE

Definition

CREATE TABLE defines a new relation: its columns, their data types, and the constraints that guard data integrity (primary keys, foreign keys, NOT NULL, UNIQUE, CHECK, DEFAULT).

sql
CREATE TABLE departments (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    salary   NUMERIC(10,2) CHECK (salary > 0),
    dept_id  INT REFERENCES departments(id),
    hired_on DATE DEFAULT CURRENT_DATE
);

How It Works

The engine registers the table's metadata (column list, types, constraints) in the system catalog and allocates initial storage. Constraints are enforced on every subsequent INSERT/UPDATE. Foreign keys like dept_id INT REFERENCES departments(id) require the referenced table (and referenced column, usually a primary/unique key) to already exist — order matters when creating related tables.

A genuinely useful pattern is CREATE TABLE AS SELECT (CTAS), which creates a new table populated from a query in one shot:

sql
CREATE TABLE high_earners AS
SELECT id, name, salary
FROM employees
WHERE salary > 100000;

This copies both structure (inferred from the query) and data, but not constraints or indexes from the source — those must be added manually afterward if needed.

Edge Cases and Pitfalls

• Forward references fail: you cannot create employees referencing departments before departments exists, unless you create the FK afterward via ALTER TABLE ... ADD CONSTRAINT (useful for circular references between two tables).
• `IF NOT EXISTS` avoids errors on re-run but silently skips creation even if your column definitions changed — it does not reconcile schema drift.
• Reserved words (e.g. order, user, group) as table/column names require quoting ("order") and are best avoided entirely.
• Default column widths matter: an undersized VARCHAR(20) for names or emails is a common early design mistake that later requires a (potentially locking) ALTER TABLE.
• CTAS silently drops the source table's constraints, indexes, and identity/auto-increment behavior — a frequent surprise for people who expect a "clone".

Key Takeaways / Q&A

Q: Does CREATE TABLE ... AS SELECT copy indexes and constraints? A: No — only column names and inferred data types (and the data itself). Primary keys, foreign keys, and indexes must be added separately.

Q: What's the fix for two tables that need to reference each other (a circular foreign key)? A: Create both tables without the problematic FK first, then add it afterward with ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ....

Q: Is CREATE TABLE reversible? A: The statement itself just adds an object; removing it later means DROP TABLE, which is destructive (see 9.5) — so review column types and constraints carefully before creating, especially on tables that will soon hold real data.