Postgres ADD COLUMN IF NOT EXISTS: Syntax and Examples

ALTER TABLE users
ADD COLUMN IF NOT EXISTS phone VARCHAR(20);

Introduction

PostgreSQL makes adding columns fast and safe. With volatile and non-volatile defaults handled differently, understanding the behavior helps you avoid downtime. The examples below use a simple users, orders and products schema and were run on PostgreSQL 16.

Add Column IF NOT EXISTS

Safely add a column without error if it already exists. Useful in migrations that may run more than once.

-- Won't error if column exists:
ALTER TABLE users
ADD COLUMN IF NOT EXISTS phone VARCHAR(20);

-- Multiple safe additions in one statement:
ALTER TABLE users
ADD COLUMN IF NOT EXISTS phone VARCHAR(20),
ADD COLUMN IF NOT EXISTS avatar_url TEXT;

Each ADD COLUMN clause needs its own IF NOT EXISTS. Clauses for columns that already exist are skipped with a NOTICE, and the rest are added. When the column already exists, psql prints:

NOTICE:  column "phone" of relation "users" already exists, skipping
ALTER TABLE

With DEFAULT and NOT NULL

IF NOT EXISTS combines with any column definition. To add a NOT NULL column to a table that already has rows, give it a DEFAULT so existing rows get a value:

ALTER TABLE users
ADD COLUMN IF NOT EXISTS is_verified BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT now();

Without the DEFAULT, adding a NOT NULL column to a non-empty table fails with ERROR: column "..." of relation "users" contains null values.

If the column exists with a different type

IF NOT EXISTS only compares the column name. If phone already exists as VARCHAR(20), this statement does nothing and the type stays VARCHAR(20):

-- Skipped with a NOTICE; phone is still VARCHAR(20):
ALTER TABLE users
ADD COLUMN IF NOT EXISTS phone TEXT;

-- To actually change the type, use ALTER COLUMN:
ALTER TABLE users
ALTER COLUMN phone TYPE TEXT;

The same applies to defaults and constraints: an existing column keeps them. Change them with ALTER COLUMN ... SET DEFAULT, SET NOT NULL or ADD CONSTRAINT.

Check the column with \d or information_schema

In psql, \d users lists every column with its type, nullability and default:

\d users

In a script or another client, query information_schema.columns:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'users'
  AND column_name = 'phone';

Before PostgreSQL 9.6: DO Block Fallback

PostgreSQL 9.5 and older reject ADD COLUMN IF NOT EXISTS with a syntax error. Check information_schema.columns in a DO block instead:

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'users'
      AND column_name = 'phone'
  ) THEN
    ALTER TABLE users ADD COLUMN phone VARCHAR(20);
  END IF;
END
$$;

This also works on newer versions, so it is an option when one migration has to run on old and new servers. On 9.6 and later, the plain IF NOT EXISTS form is shorter and does the same thing.

Add a Single Column

Basic column addition with optional default value.

-- Add a nullable column (default is NULL):
ALTER TABLE users
ADD COLUMN nickname VARCHAR(50);

-- Add NOT NULL column with default:
ALTER TABLE users
ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

Add Generated (Computed) Column

PostgreSQL 12+ supports generated columns that auto-compute from other columns.

-- Stored generated column:
ALTER TABLE products
ADD COLUMN profit NUMERIC
GENERATED ALWAYS AS (price - cost) STORED;

-- Full name from first + last:
ALTER TABLE users
ADD COLUMN full_name TEXT
GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

Add Multiple Columns

Add several columns in a single statement.

ALTER TABLE orders
ADD COLUMN tracking_number VARCHAR(100),
ADD COLUMN shipped_at TIMESTAMPTZ,
ADD COLUMN carrier TEXT DEFAULT 'unknown';

Add Column with Constraint

Add columns with CHECK constraints, foreign keys, or unique constraints.

-- With CHECK constraint:
ALTER TABLE products
ADD COLUMN rating SMALLINT DEFAULT 0
CONSTRAINT chk_rating CHECK (rating BETWEEN 0 AND 5);

-- With foreign key:
ALTER TABLE orders
ADD COLUMN warehouse_id INTEGER
REFERENCES warehouses(id);

-- With unique constraint:
ALTER TABLE users
ADD COLUMN username TEXT UNIQUE;

Best Practices

• Use IF NOT EXISTS for idempotent migrations
• Constant defaults are fast (PG 11+): no table rewrite
• On PostgreSQL 9.5 and older, use a DO block that checks information_schema.columns
• For large tables, add foreign keys with NOT VALID then VALIDATE CONSTRAINT separately
• Use generated columns for computed values instead of triggers
• Wrap DDL in transactions (PostgreSQL supports transactional DDL)

Supabase runs on PostgreSQL, so the same ALTER TABLE syntax applies there. For ready-made queries, see these Supabase SQL examples and the Supabase auth.users SQL template.

Generate PostgreSQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct PostgreSQL query instantly.

Frequently Asked Questions