Create Table

The CREATE TABLE statement is how you define the structure of a new table in PostgreSQL. Every column requires a name and a data type, and you can attach constraints to columns or to the whole table to enforce data integrity rules. Getting your table definition right at the start saves you from costly migrations later.

CREATE TABLE syntax

The basic syntax is:

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

Key points:

• The table name must be unique within its schema. PostgreSQL defaults to the public schema.
• IF NOT EXISTS causes PostgreSQL to issue 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 (defined after the columns) can reference multiple columns, such as composite primary keys or multi-column unique constraints.

CREATE TABLE example

The following statement creates an accounts table with several constraints:

CREATE TABLE accounts (
  user_id    SERIAL PRIMARY KEY,
  username   VARCHAR(50)  UNIQUE NOT NULL,
  password   VARCHAR(50)  NOT NULL,
  email      VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP    NOT NULL,
  last_login TIMESTAMP
);

After running the statement you should see:

CREATE TABLE

Inspect the resulting structure in psql with d accounts:

Table "public.accounts"
   Column   |            Type             | Nullable |                  Default
------------+-----------------------------+----------+-------------------------------------------
 user_id    | integer                     | not null | nextval('accounts_user_id_seq'::regclass)
 username   | character varying(50)       | not null |
 password   | character varying(50)       | not null |
 email      | character varying(255)      | not null |
 created_at | timestamp without time zone | not null |
 last_login | timestamp without time zone |          |
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (user_id)
    "accounts_email_key" UNIQUE CONSTRAINT, btree (email)
    "accounts_username_key" UNIQUE CONSTRAINT, btree (username)

Notice that SERIAL automatically creates a sequence and sets it as the column default. The UNIQUE constraints also implicitly create indexes.

CREATE TABLE best practices

• Always specify NOT NULL on columns that should never be empty — 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 in high-volume tables to avoid exhausting the INTEGER range.
• Declare FOREIGN KEY constraints to maintain referential integrity between related tables.
• Use TIMESTAMPTZ instead of TIMESTAMP for columns recording real-world event times to avoid time-zone ambiguity.
• Use IF NOT EXISTS in deployment scripts so they remain idempotent and safe to re-run.

Reference: PostgreSQL documentation — CREATE TABLE.