How to create your first table in PostgreSQL

Creating Tables

A table is a named set of typed columns. Get the schema right before inserting data — changing columns later on large tables is expensive.

Basic syntax

CREATE TABLE users (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT    NOT NULL UNIQUE,
  username   TEXT    NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

Safe creation

CREATE TABLE IF NOT EXISTS users ( ... );

The IF NOT EXISTS clause prevents an error if the table already exists — useful in migration scripts.

Constraints in-line vs table-level

-- Inline (single column)
created_at TIMESTAMPTZ NOT NULL DEFAULT now()

-- Table-level (composite)
CONSTRAINT pk_order PRIMARY KEY (order_id, product_id)

Drop and truncate

DROP TABLE users;            -- removes table and data
DROP TABLE IF EXISTS users;  -- safe version
TRUNCATE users;              -- deletes all rows, keeps table

TRUNCATE is much faster than DELETE FROM users on large tables because it does not scan rows individually. Use it in tests when resetting state, but never in production without a transaction you can roll back.