CREATE TABLE

Define the structure of a table with columns, data types, and inline constraints.

Overview

CREATE TABLE is the most fundamental DDL (Data Definition Language) statement. It defines the structure of a new table: its column names, the data type of each column, and optional inline constraints (NOT NULL, DEFAULT, UNIQUE, CHECK, PRIMARY KEY, REFERENCES). A well-designed table structure makes queries faster, enforces data quality automatically, and reduces application-level validation. PostgreSQL executes CREATE TABLE transactionally if it runs inside a BEGIN...COMMIT block and you roll back, the table is removed. This is a significant advantage over databases like MySQL where DDL causes an implicit commit. Beyond basic columns, CREATE TABLE supports column-level and table-level constraints, generated columns (computed from other columns), and table inheritance. For production tables, always specify NOT NULL on columns that should never be empty and choose sensible DEFAULT values to reduce INSERT noise.

Key Points

•Columns are defined as: column_name data_type [constraints]
•PRIMARY KEY can be defined inline (single column) or as a table constraint (composite)
•NOT NULL prevents NULL values; DEFAULT sets the value when INSERT omits the column
•REFERENCES creates a foreign key linking to another table
•Table names and column names are case-insensitive unless quoted with double-quotes
•CREATE TABLE IF NOT EXISTS avoids an error if the table already exists

Examples

A basic table with common constraints

CREATE TABLE users (
  id         SERIAL        PRIMARY KEY,
  email      TEXT          NOT NULL UNIQUE,
  username   VARCHAR(50)   NOT NULL,
  created_at TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  is_active  BOOLEAN       NOT NULL DEFAULT TRUE
);

Table with a composite primary key and foreign key

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

CREATE TABLE IF NOT EXISTS

CREATE TABLE IF NOT EXISTS settings (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

Useful in migration scripts that might be re-run.

CREATE TABLE

Overview

Key Points

Examples

Exercises

Knowledge Check

Where can a PRIMARY KEY constraint be defined at the column level, table level, or both?