Skip to content

Add Columns in PostgreSQL with ALTER TABLE: Syntax and Examples

add column in existing table

Quick answer: Use ALTER TABLE employees ADD COLUMN phone text; to add a PostgreSQL column. To add several, use ALTER TABLE employees ADD COLUMN phone text, ADD COLUMN active boolean DEFAULT true;.

Syntax

ALTER TABLE table_name
ADD COLUMN column_name data_type DEFAULT default_value;

Replace table_name, column_name and data_type with your actual identifiers and type. The DEFAULT clause is optional. PostgreSQL uses ADD COLUMN, not ADD COLUMN_NAME; COLUMN may be omitted, but including it is clearer.

For example, consider the following statement:

ALTER TABLE actors ADD COLUMN actors_age integer NOT NULL DEFAULT 0;
This example works for existing rows because PostgreSQL supplies the non-null default. Choose a meaningful default rather than using zero simply to bypass validation.

Syntax for adding multiple columns

ALTER TABLE employees
  ADD COLUMN phone text,
  ADD COLUMN active boolean NOT NULL DEFAULT true,
  ADD COLUMN hired_on date;

Add a column with the NOT NULL constraint to a table that already has data

Adding a new NOT NULL column without a non-null default to a populated table generally fails, because existing rows would receive NULL. Adding the column with a suitable non-null default is different and can succeed. PostgreSQL rejects the change when existing rows would violate the new constraint. For details, see the official ALTER TABLE reference.

For example, let’s take an example that we have a ‘directors’ table which has data on it and we want to add ‘contact_number’ column in that.

ALTER TABLE directors
ADD COLUMN contact_number text NOT NULL;

then it will throw an error:

ERROR:  column "contact_number" contains null values
SQL state: 23502

Existing rows have no value for the new column, so they would violate its NOT NULL constraint. Store telephone numbers as text, not integer, to preserve leading zeros, plus signs and formatting.

For a real contact-number field, add a nullable text column, populate it with verified values, and only then enforce NOT NULL if every director must have a number.

Check for missing values before enforcing the constraint: SELECT count(*) FROM directors WHERE contact_number IS NULL;. When the count is zero, run ALTER TABLE directors ALTER COLUMN contact_number SET NOT NULL;. Do not invent placeholder phone numbers to satisfy the constraint.

Adding column using pgAdmin4

In pgAdmin 4, expand the target database, schema and table, then use the Columns node to create a column. The screenshots show an older interface, so labels may differ in newer versions.

Select the provided table to add a column and then right click on the Column.

Now click on the Create option and then click to Column.

pgAdmin 4 context menu for creating a column under a table
Step1-adding column in existing table

In the next step, provide the column name. Let’s create a column with ‘actors_age’ column name.

pgAdmin 4 dialog for entering a new column name
Step2-adding column in existing table

Under the Definition, select the datatype from the drop-down list, you can also provide length of the datatype as well.

pgAdmin 4 column definition tab showing data type settings
Step3-adding column in existing table

You can also add Constraints if you need. Here I have set the default value as 0.

pgAdmin 4 column settings with a default value
Step4-adding column in existing table

Now click on the SAVE button, to create it.

pgAdmin 4 Save action for a newly defined column
Step5-adding column in existing table

In PostgreSQL you cannot define the new column’s place in the table, it will append it to the end of the table.

Try a reversible ALTER TABLE example

Run this exercise in a test database, not against production data. A transaction allows you to inspect the new columns and then roll back the changes.

BEGIN;
CREATE TEMP TABLE demo_staff (id integer PRIMARY KEY, name text NOT NULL);
INSERT INTO demo_staff VALUES (1, 'Asha'), (2, 'Ravi');
ALTER TABLE demo_staff
  ADD COLUMN active boolean NOT NULL DEFAULT true,
  ADD COLUMN phone text;
SELECT id, name, active, phone FROM demo_staff ORDER BY id;
ROLLBACK;

Expected result: both rows have active = true and phone = NULL. The rollback removes the temporary test table and its changes within this transaction. If you already have a transaction open, run the exercise in a separate test session instead.

Important Details and Troubleshooting

PostgreSQL does not support SQL Server’s WITH VALUES syntax for adding columns. For a populated table, backfill a new column before setting NOT NULL if no suitable default exists.

Safely add a column to a large production table

ALTER TABLE normally takes a strong table lock, so plan schema changes during low-traffic periods and test them on a copy first. Adding a column with a constant default is optimized in modern PostgreSQL releases, but not every default expression is equally cheap. Check your PostgreSQL version and migration requirements before assuming a change is instant.

Verify the change and avoid common errors

  • Column already exists: use ADD COLUMN IF NOT EXISTS for repeatable migrations, while still checking that an existing column has the expected type.
  • Permission denied: the executing role generally must own the table or have appropriate ownership privileges.
  • Existing rows violate NOT NULL: add a nullable column, backfill real values, then set the constraint; alternatively supply an appropriate non-null default.
  • Check the result: inspect the table definition in pgAdmin or query information_schema.columns for the target table.

Frequently asked questions

Can PostgreSQL add multiple columns in one statement?

Yes. Separate multiple ADD COLUMN clauses with commas, as shown above.

Can I insert a new column between existing columns?

PostgreSQL appends newly added columns to the physical column order. Specify the preferred display order in your SELECT query or view instead of rebuilding a table just to reorder columns.

Does a default value apply to existing rows?

When you add a column with a default, existing rows read as having that default. Storage and rewrite behavior depend on the default expression and PostgreSQL version. See the official ALTER TABLE documentation for current details.

Related guides: CREATE TABLE in PostgreSQL: Syntax, Constraints and Examples · PostgreSQL Constraints Explained: PRIMARY KEY, FOREIGN KEY and CHECK · SQL DISTINCT in PostgreSQL: Remove Duplicate Results with Examples

Leave a Reply

Your email address will not be published. Required fields are marked *