Create Table in PostgreSQL

The CREATE TABLE statement defines the structure of a new table in PostgreSQL. Every column requires a name and a data type, and you attach constraints to enforce data integrity. Getting the table definition right at the start saves costly migrations later.

Syntax

CREATE TABLE [IF NOT EXISTS] table_name (
  column1 datatype [(length)] column_constraint,
  column2 datatype [(length)] column_constraint,
  ...
  table_constraint
);

Key points:

• The table name must be unique within its schema. PostgreSQL defaults to the public schema.
• IF NOT EXISTS issues a notice rather than an error if the table already exists.
• Column constraints include NOT NULL, UNIQUE, PRIMARY KEY, CHECK, DEFAULT, and REFERENCES (foreign key).
• Table-level constraints (declared after the columns) can reference multiple columns, such as composite primary key or multi-column unique constraints.

Practical Example

Create an events table with multiple constraint types:

CREATE TABLE venues (
  venue_id   SERIAL PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  city       VARCHAR(100) NOT NULL
);

CREATE TABLE events (
  event_id     BIGSERIAL PRIMARY KEY,
  title        VARCHAR(300)   NOT NULL,
  slug         VARCHAR(300)   UNIQUE NOT NULL,
  venue_id     INT            NOT NULL REFERENCES venues(venue_id),
  starts_at    TIMESTAMPTZ    NOT NULL,
  ends_at      TIMESTAMPTZ    NOT NULL,
  ticket_price NUMERIC(10, 2) NOT NULL DEFAULT 0,
  published    BOOLEAN        NOT NULL DEFAULT FALSE,
  CONSTRAINT chk_event_dates CHECK (ends_at > starts_at)
);

After creation, inspect the structure in psql:

\d events

               Table "public.events"
    Column    |            Type             | Nullable |
--------------+-----------------------------+----------+
 event_id     | bigint                      | not null |
 title        | character varying(300)      | not null |
 slug         | character varying(300)      | not null |
 venue_id     | integer                     | not null |
 starts_at    | timestamp with time zone    | not null |
 ends_at      | timestamp with time zone    | not null |
 ticket_price | numeric(10,2)               | not null |
 published    | boolean                     | not null |
Indexes:
    "events_pkey" PRIMARY KEY, btree (event_id)
    "events_slug_key" UNIQUE CONSTRAINT, btree (slug)
Check constraints:
    "chk_event_dates" CHECK (ends_at > starts_at)
Foreign-key constraints:
    "events_venue_id_fkey" FOREIGN KEY (venue_id) REFERENCES venues(venue_id)

Constraint Types

Testing with Vela

When designing a new table schema, Vela database branching lets you iterate safely: create a branch, apply your CREATE TABLE and INSERT test data, run your application against it, then refine the schema before committing the design to production. Branches are instant copy-on-write clones — no separate staging database needed.

Production Tips

• Always specify NOT NULL on required columns — it is cheaper to enforce this at the database level than in application code.
• Use BIGSERIAL or BIGINT GENERATED ALWAYS AS IDENTITY for primary keys on high-volume tables to avoid hitting the 2-billion row limit of INTEGER.
• Declare foreign key constraints to maintain referential integrity between related tables from day one.
• Use TIMESTAMPTZ instead of TIMESTAMP for event-time columns to avoid time-zone ambiguity.
• Use IF NOT EXISTS in deployment scripts so they remain idempotent and safe to re-run.
• Name your constraints explicitly with CONSTRAINT name — auto-generated names like events_check are harder to reference in later migrations.