CREATE TABLE

CREATE TABLE defines a new table: its name, its columns, each column's data type, and any constraints that should be enforced on the data going in. This single statement is where most of a schema's design decisions actually get written down.

Syntax

General shape of CREATE TABLE

SQL

CREATE TABLE table_name (
    column1  data_type  constraints,
    column2  data_type  constraints,
    column3  data_type  constraints,
    ...
    table_level_constraint
);

A complete worked example

Here is a realistic users table that mixes several column types with constraints defined inline, right next to the column they apply to.

A users table with columns, types, and constraints

SQL

CREATE TABLE users (
    id            SERIAL PRIMARY KEY,
    email         VARCHAR(255) NOT NULL UNIQUE,
    full_name     VARCHAR(150) NOT NULL,
    age           SMALLINT CHECK (age >= 0),
    is_active     BOOLEAN NOT NULL DEFAULT TRUE,
    signup_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    referred_by   INTEGER REFERENCES users(id),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Reading this column by column: id is an auto-incrementing integer and the primary key. email is required and must be unique across all rows. full_name is required text. age is optional but, if provided, must be zero or greater. is_active defaults to TRUE if not specified. signup_date defaults to today's date. referred_by is a foreign key pointing back at another row in the same table. created_at records the exact moment the row was inserted, timezone-aware.

IF NOT EXISTS — safe to re-run

Running CREATE TABLE users (...) a second time against a database that already has a users table raises an error. Adding IF NOT EXISTS makes the statement a no-op instead of an error when the table is already there — handy for setup scripts and migrations that might be run more than once.

Idempotent table creation

SQL

CREATE TABLE IF NOT EXISTS users (
    id    SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);
-- Running this twice in a row succeeds both times

CREATE TABLE ... AS SELECT

You can also create a new table directly from the result of a query. The new table's columns and data types are inferred from the query, and it is populated immediately with the query's rows. This is useful for snapshotting data, building a quick summary table, or staging a filtered subset of a larger table.

Creating a table from a SELECT

SQL

CREATE TABLE active_users AS
SELECT id, email, full_name
FROM users
WHERE is_active = TRUE;
-- active_users now exists as its own independent table with a snapshot
-- of matching rows; it does not stay in sync with future changes to users.

Constraints are covered in depth elsewhere

`PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`, `NOT NULL`, `CHECK`, and `DEFAULT` each have a dedicated page under Data Integrity & Constraints that goes into the details and edge cases — this page focuses on the `CREATE TABLE` statement itself.

Column order in the definition generally does not affect query behavior, but it does affect the default column order in SELECT * and in some export tools.
Table and column names are case-insensitive by default in most dialects unless quoted, but conventions vary — pick a convention (like snake_case) and stick to it.
CREATE TABLE ... AS SELECT copies data, not constraints — the new table typically has no primary key, foreign keys, or NOT NULL constraints unless you add them afterward.

Tip

Sketch the columns and their types on paper (or in a comment block) before writing the `CREATE TABLE` statement for anything non-trivial — it's much cheaper to rename a column on paper than after the table has production data in it.