CREATE TABLE

Defines a new table: its columns, types, defaults and constraints.

Syntax

CREATE TABLE [IF NOT EXISTS] name (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  col        type [NOT NULL] [DEFAULT expr] [UNIQUE] [CHECK (...)] [REFERENCES other (col)],
  calc       type GENERATED ALWAYS AS (expr) [STORED | VIRTUAL],
  ...,
  [CONSTRAINT name] table_constraint
);
CREATE TABLE name AS SELECT ...;        -- copy a query result (no constraints)
CREATE TEMP TABLE name (...);           -- dropped at end of session

Choose the most specific type (date not text, numeric(12,2) for money, boolean for flags).
Identity columns (GENERATED ALWAYS AS IDENTITY) are the modern replacement for serial.
DEFAULT supplies a value when an INSERT omits the column (DEFAULT now(), DEFAULT false).
Generated columns compute from other columns. In PostgreSQL 18 they are VIRTUAL (computed on read) by default; STORED writes them to disk (required if you want to index them).
Constraints can be written on the column or as table constraints (needed for multi-column keys).
CREATE TABLE ... AS SELECT copies data and column types but no constraints, defaults or indexes. CREATE TABLE new (LIKE old INCLUDING ALL) copies the structure.
Name tables and columns in lower_snake_case to avoid quoting.

Examples

A complete table (modifies schema) starter

CREATE TABLE product_reviews (
  review_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  integer NOT NULL REFERENCES products (product_id),
  customer_id integer NOT NULL REFERENCES customers (customer_id),
  rating      smallint NOT NULL CHECK (rating BETWEEN 1 AND 5),
  body        text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (product_id, customer_id)
);

INSERT INTO product_reviews (product_id, customer_id, rating, body)
VALUES (1, 1, 5, 'Fresh and fragrant')
RETURNING review_id, rating, created_at IS NOT NULL AS has_timestamp;

review_id and created_at are filled automatically.

Generated column (modifies schema) starter

CREATE TABLE order_lines_demo (
  quantity   integer NOT NULL,
  unit_price numeric(12,2) NOT NULL,
  line_total numeric(14,2) GENERATED ALWAYS AS (quantity * unit_price)
);
INSERT INTO order_lines_demo (quantity, unit_price) VALUES (3, 19.50);
SELECT * FROM order_lines_demo;

line_total is computed (virtual by default in PostgreSQL 18): 58.50.

Pitfalls

Storing dates or numbers as text.
Missing primary key, so duplicates creep in.
Using float types for money.
Quoted mixed-case names ("OrderDate") that must then always be quoted.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: AUTO_INCREMENT instead of identity; no separate boolean type (BOOLEAN = TINYINT(1)).
SQL Server: IDENTITY(1,1); bit for booleans; datetime2 for timestamps; nvarchar for Unicode text.
SQLite: INTEGER PRIMARY KEY is the rowid alias; column types are affinities unless the table is STRICT.
Oracle: GENERATED AS IDENTITY (12c+); VARCHAR2 and NUMBER types; BOOLEAN only since 23ai.