Creating Tables

CREATE TABLE is where a schema becomes real. It names the table, lists its columns with their types, and can attach constraints — rules the database will enforce on every row — right alongside each column. This page walks through the full syntax using the sample e-commerce schema (customers, products, orders, order_items) that recurs throughout these PostgreSQL pages, plus a few shortcuts for creating tables from existing structure or query results.

Basic syntax

At minimum, CREATE TABLE needs a name and a comma-separated list of columns, each with a data type. Constraints such as PRIMARY KEY, NOT NULL, UNIQUE, and CHECK can be written directly after a column's type (a column constraint), or as a separate line at the end of the list (a table constraint) when a rule spans more than one column.

The products table

SQL

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  sku          TEXT NOT NULL UNIQUE,
  name         TEXT NOT NULL,
  description  TEXT,
  unit_price   NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
  stock_qty    INTEGER NOT NULL DEFAULT 0,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Each piece here is doing real work: SERIAL PRIMARY KEY auto-generates a unique integer id, UNIQUE keeps sku values from colliding across rows, the CHECK constraint stops a negative price from ever being stored, and DEFAULT now() timestamps a row automatically at insert time if no value is supplied.

The same pattern extends across the rest of the sample schema — customers, orders, and order_items each declare their own columns and constraints, with foreign keys tying them together (covered in full on the foreign-key page).

The rest of the sample schema

SQL

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  order_date  TIMESTAMPTZ NOT NULL DEFAULT now(),
  status      TEXT NOT NULL DEFAULT 'pending'
);

CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,
  order_id      INTEGER NOT NULL REFERENCES orders(order_id),
  product_id    INTEGER NOT NULL REFERENCES products(product_id),
  quantity      INTEGER NOT NULL CHECK (quantity > 0),
  unit_price    NUMERIC(10, 2) NOT NULL
);

IF NOT EXISTS

Running CREATE TABLE twice for the same name normally raises an error. Adding IF NOT EXISTS makes the statement a no-op when the table already exists, instead of failing — handy in setup scripts and migrations that might run more than once.

Safe to re-run

SQL

CREATE TABLE IF NOT EXISTS products (
  product_id SERIAL PRIMARY KEY,
  sku        TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL
);

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

Copying structure with LIKE

CREATE TABLE ... (LIKE other_table) creates a new, empty table with the same column names and types as an existing one. It is a quick way to build a staging table or an archive table that mirrors a production table's shape without retyping every column.

Copying just the column structure

SQL

CREATE TABLE products_staging (LIKE products);

LIKE copies structure, not constraints, by default

Plain LIKE only copies column names and types. To also bring along defaults, NOT NULL, or CHECK constraints, add the relevant options — for example LIKE products INCLUDING DEFAULTS INCLUDING CONSTRAINTS. Indexes and foreign keys need their own INCLUDING clauses too, or they are left out entirely.

CREATE TABLE AS SELECT

CREATE TABLE ... AS SELECT (often abbreviated CTAS) builds a brand-new table populated with the result of a query, right at creation time. This is useful for snapshotting a report, materializing an expensive aggregation, or spinning up a working copy of a filtered subset of data.

Snapshotting high-value orders

SQL

CREATE TABLE big_orders AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'completed';

SELECT 1204

CTAS does not copy constraints or indexes either

Like LIKE, CREATE TABLE AS SELECT only copies the resulting column names and inferred types — no primary key, no foreign keys, no indexes come along automatically. Add those separately with ALTER TABLE and CREATE INDEX if the new table needs them.

CREATE TABLE names a table and lists its columns, types, and constraints.
Column constraints (NOT NULL, UNIQUE, CHECK) attach right after a type; table constraints stand on their own line for rules spanning multiple columns.
IF NOT EXISTS makes table creation safe to re-run.
LIKE copies column structure from an existing table; CREATE TABLE AS SELECT populates a new table from a query.