How to CREATE TABLE in PostgreSQL

How to CREATE TABLE in PostgreSQL

CREATE TABLE is PostgreSQL’s workhorse for building schemas. Mastering its syntax lets you design stable, efficient databases that fit your application from day one.

What does CREATE TABLE do in PostgreSQL?

CREATE TABLE builds a brand-new table in the current database. You specify each column’s name, data type, defaults, and constraints so PostgreSQL can enforce data integrity automatically.

What is the basic syntax?

The core pattern is CREATE TABLE table_name (column_definitions, table_constraints);. Brackets hold comma-separated column specs followed by any table-level constraints.

How do I define columns and data types?

Start every column with a name and a type: id SERIAL, email TEXT, price NUMERIC(10,2). Add modifiers such as NOT NULL or DEFAULT now() right after the type.

How can I add constraints?

PRIMARY KEY

Declare one or more columns as the unique identifier: PRIMARY KEY(id) or inline id SERIAL PRIMARY KEY.

FOREIGN KEY

Reference another table to maintain relational integrity: FOREIGN KEY (user_id) REFERENCES users(id).

UNIQUE & CHECK

Use UNIQUE(email) to prevent duplicates and CHECK(price > 0) to enforce logical rules.

How do I create a table from existing data?

Combine CREATE TABLE with AS SELECT to clone structure and rows: CREATE TABLE active_users AS SELECT * FROM users WHERE is_active; No constraints are copied; add them later with ALTER TABLE.

Best practices for CREATE TABLE

Use meaningful names, pick the smallest sufficient data type, prefer GENERATED ALWAYS AS IDENTITY over old SERIAL, and index foreign keys immediately to avoid future scans.

Common mistakes and how to avoid them

Don’t forget schema qualification (public.orders) in multi-schema databases. Avoid unbounded VARCHAR; cap it to expected length for faster checks.

Full working example

CREATE TABLE public.orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL,
status TEXT NOT NULL DEFAULT 'pending',
total_cents INTEGER CHECK (total_cents >= 0),
created_at TIMESTAMPTZ DEFAULT now(),
FOREIGN KEY (user_id) REFERENCES public.users(id)
);


What comes after CREATE TABLE?

Immediately seed lookup tables, create needed indexes, and write sample inserts to validate constraints. Keep your DDL in version control for repeatable deployments.

FAQ

Can I add columns later?

Yes. Run ALTER TABLE ... ADD COLUMN. PostgreSQL fills existing rows with the column’s default or NULL.

How do I drop a table safely?

Issue DROP TABLE IF EXISTS table_name CASCADE; to remove it and any dependent objects. Use cautiously in production.

What’s the difference between SERIAL and IDENTITY?

SERIAL creates a sequence behind the scenes, while GENERATED ... AS IDENTITY is SQL-standard, easier to manage, and preferred for new designs.