CREATE TABLE name (column type constraints, ...) creates a table. Each column gets a name and a data type, then optional constraints such as PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE or CHECK.
The id column numbers itself, plan falls back to 'free' when the insert leaves it out, and created_at records the time of the insert. The data types page lists the common column types.
Column constraints
| Constraint | Effect |
|---|---|
PRIMARY KEY | Unique and not null; one per table |
NOT NULL | The column must have a value |
DEFAULT expr | Value used when an insert omits the column |
UNIQUE | No two rows share a value (several NULLs are allowed) |
CHECK (expr) | Rejects rows where the expression is false |
REFERENCES t (col) | Foreign key to another table |
Since PostgreSQL 15, UNIQUE NULLS NOT DISTINCT treats NULLs as equal, so only one row can have a NULL in that column.
Rules that span several columns
A constraint on more than one column goes on its own line after the columns. Naming it with CONSTRAINT name makes error messages readable and lets you drop it by name later.
The query lists the three constraints. The unnamed ones got generated names, bookings_guests_check and bookings_pkey.
Create a table only if it does not exist
IF NOT EXISTS turns the second CREATE TABLE into a notice instead of an error. It checks only the name. If a table called logs already exists with different columns, nothing changes.
Create a table from a query or from another table
CREATE TABLE ... AS creates a table from the result of a SELECT, with the result's columns and types. LIKE copies another table's definition without its rows, and INCLUDING ALL brings its defaults, constraints, identity and indexes along.
big_orders has one row. orders_archive has the columns and constraints of orders and no rows.
Generated columns
A generated column is computed from other columns of the same row, and inserts cannot write it. In PostgreSQL 17 it must be STORED: the value is computed on write and saved on disk.
The totals are 13.5 and 20.
Temporary and unlogged tables
CREATE TEMP TABLE creates a table that only the current session can see, dropped when the session ends. It suits intermediate results in a long script. CREATE UNLOGGED TABLE skips the write-ahead log, so writes are faster. The table is emptied after a crash and is not copied to replicas, so use it only for data you can rebuild.
CREATE TEMP TABLE import_batch (line text);
CREATE UNLOGGED TABLE page_views_staging (path text, seen_at timestamptz);
To change a table after it exists, use ALTER TABLE. Every option is listed in the CREATE TABLE reference.
Frequently Asked Questions
Are PostgreSQL table names case sensitive?
Unquoted names are folded to lowercase, so CREATE TABLE Users creates users. A quoted name like "Users" keeps its case and must then be quoted in every query, which is why lowercase snake_case names are the norm.
How do I see the structure of a table in PostgreSQL?
In psql, \d table_name shows the columns, types, defaults, indexes and constraints. From SQL, query information_schema.columns WHERE table_name = 'table_name'.
How do I copy a table in PostgreSQL?
CREATE TABLE copy AS TABLE original; copies the columns and rows but no constraints or indexes. CREATE TABLE copy (LIKE original INCLUDING ALL); copies the structure with defaults, constraints and indexes but no rows.
How do I drop a table in PostgreSQL?
DROP TABLE table_name; removes the table and its data. DROP TABLE IF EXISTS table_name; skips the error when it does not exist, and CASCADE also drops views and foreign keys that depend on it.