How to Add a Column in PostgreSQL

How do I add a column to a PostgreSQL table?

ALTER TABLE ... ADD COLUMN adds a new field to an existing PostgreSQL table without recreating the table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does ALTER TABLE ADD COLUMN do?

The command appends a new column definition to an existing table. PostgreSQL rewrites metadata, optionally back-fills default values, and preserves all existing data.

What is the exact syntax?

See the syntax section below. Square brackets denote optional keywords.

How do I add a simple nullable column?

Run ALTER TABLE users ADD COLUMN bio text;.Because no NOT NULL or DEFAULT is provided, the column starts as NULL for all rows.

How do I add a NOT NULL column safely?

Step 1: ALTER TABLE users ADD COLUMN is_active boolean DEFAULT true; (back-fills value). Step 2: ALTER TABLE users ALTER COLUMN is_active SET NOT NULL;. Splitting prevents table rewrites on large tables pre-PostgreSQL 11.

Can I add multiple columns at once?

Yes.Separate definitions with commas: ALTER TABLE sales ADD COLUMN region text, ADD COLUMN created_at timestamptz DEFAULT now();

Will ADD COLUMN lock my table?

In PostgreSQL 12+, adding a NULLABLE column with no default is almost instant, requiring only a brief ACCESS EXCLUSIVE lock on metadata.Adding a column with a default before v11 rewrites the table, causing longer locks.

Best practices to avoid downtime?

1) Add the column nullable; 2) back-fill in batches; 3) add constraints after data is populated; 4) use SET NOT NULL later.Consider pg_backfill or logical replication for very large tables.

Example use cases

• Store last login time
• Flag soft-deleted rows
• Track JSON configuration

When should I use GENERATED columns?

If the new value is derived from other columns, declare GENERATED ALWAYS AS (expression) STORED instead of triggers.

How do I remove a column if needed?

Use ALTER TABLE table_name DROP COLUMN column_name;. Verify no views or functions depend on it.

Why How to Add a Column in PostgreSQL is important

How to Add a Column in PostgreSQL Example Usage


ALTER TABLE public.users
ADD COLUMN last_login timestamptz DEFAULT now();

How to Add a Column in PostgreSQL Syntax


ALTER TABLE table_name
    ADD [COLUMN] column_name data_type
    [COLLATE collation]
    [ { NOT NULL | NULL } ]
    [ { DEFAULT default_expr | GENERATED ALWAYS AS ( expr ) STORED } ]
    [ CONSTRAINT constraint_name ]
    [ column_constraint [ ... ] ]
    [ CASCADE | RESTRICT ];

Common Mistakes

Adding a NOT NULL column without a default on a non-empty table. PostgreSQL rejects the command because existing rows would violate NOT NULL. Fix: supply a DEFAULT or add the column nullable, back-fill, then set NOT NULL.
Expecting an instant operation when adding a column with a DEFAULT on PostgreSQL 10 or older. The server rewrites the entire table, causing long locks. Fix: upgrade to ≥11 or split the operation into add-nullable, back-fill, and set default.