Add Column

The ALTER TABLE ... ADD COLUMN statement extends an existing PostgreSQL table with one or more new columns. New columns are always appended at the end of the column list — PostgreSQL has no option to insert a column at a specific position. Adding a nullable column (or a column with a constant default in PostgreSQL 11+) is a metadata-only operation that completes instantly even on very large tables.

ADD COLUMN syntax

ALTER TABLE table_name
  ADD COLUMN column_name data_type [constraint];

To add multiple columns in one statement:

ALTER TABLE table_name
  ADD COLUMN col1 data_type constraint,
  ADD COLUMN col2 data_type constraint,
  ...;

ADD COLUMN examples

Create a sample table:

CREATE TABLE customers (
  id            SERIAL PRIMARY KEY,
  customer_name VARCHAR(255) NOT NULL
);

Add a single nullable column:

ALTER TABLE customers ADD COLUMN phone VARCHAR(25);

Add multiple columns at once:

ALTER TABLE customers
  ADD COLUMN fax   VARCHAR(25),
  ADD COLUMN email VARCHAR(400);

Verify the structure:

d customers

          Table "public.customers"
     Column      |          Type          | Nullable
-----------------+------------------------+----------
 id              | integer                | not null
 customer_name   | character varying(255) | not null
 phone           | character varying(25)  |
 fax             | character varying(25)  |
 email           | character varying(400) |

Adding a NOT NULL column to a table that already has data:

-- This fails if the table has rows:
ALTER TABLE customers ADD COLUMN contact_name VARCHAR(255) NOT NULL;
-- ERROR: column "contact_name" of relation "customers" contains null values

-- Correct approach:
-- Step 1: add without NOT NULL
ALTER TABLE customers ADD COLUMN contact_name VARCHAR(255);

-- Step 2: populate the new column
UPDATE customers SET contact_name = 'John Doe'   WHERE id = 1;
UPDATE customers SET contact_name = 'Mary Doe'   WHERE id = 2;
UPDATE customers SET contact_name = 'Lily Bush'  WHERE id = 3;

-- Step 3: enforce NOT NULL
ALTER TABLE customers ALTER COLUMN contact_name SET NOT NULL;

ADD COLUMN tips

• Adding a nullable column to a large table is safe and instant in PostgreSQL — it does not rewrite the table or block reads.
• Adding a column with a constant (non-volatile) default is also metadata-only in PostgreSQL 11+ and does not require a table rewrite.
• Never add a NOT NULL column directly to a non-empty table without a default — follow the three-step add, backfill, constrain pattern above.
• Use a single ALTER TABLE with multiple ADD COLUMN clauses to minimize the number of locks acquired.

Reference: PostgreSQL documentation — ALTER TABLE.