Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When adding not-null columns with a default value - does Postgresql optimizes this operation?

In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default value - which could be very painful, obviously. With the optimization the DB would instantaneously create the new column, store just one copy of the default value which would be returned when no non-default value is found for that column in a suitable index data structure.

For example Oracle 11g has such an optimization.

share|improve this question
SQL-Server has a similar optimization, if the default for the added column is NULL. – ypercubeᵀᴹ Mar 14 '14 at 19:08
There is no such thing. When you add a column without a default, it is fast (no actual change to the data pages). You can still win some on adding the default in the next step - this way you have to acquire row-level locks for the long operation, not an exclusive table-level one (as in the case of doing it in one step). – dezso Mar 14 '14 at 20:09
@dezso, post your comment as answer and I'll accept it (unless someone else shows up and posts contradicting evidence). – maxschlepzig Mar 14 '14 at 20:19
It'd probably be possible to implement with PostgreSQL, but has not been implemented. In general you add a column without a DEFAULT, ALTER it to add the DEFAULT afterwards so it applies to newly inserted rows, then populate the new column on existing rows by progressive batch UPDATEs. When all rows have the value you add the NOT NULL constraint. – Craig Ringer Mar 14 '14 at 23:20
@ypercube: when the default is NULL (i.e. "no default") then Postgres won't write anything to the table as well. This question is specifically for a new NOT NULL column with a default (which obviously can't be null) – a_horse_with_no_name Mar 15 '14 at 19:57
up vote 6 down vote accepted

There is no such mechanism in PostgreSQL.

However, you can still avoid the excessive effects of such a table change.

The following statement acquires an access exclusive lock on the table for the duration of the statement/transaction:

ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;

This statement changes the catalog, then rewrites the whole table so that the new column contains the default value in all rows. If the table has many rows and being accessed frequently enough, this would cause some temporary problems.

To avoid it, try to hold the exclusive lock for as short a time as possible:

ALTER TABLE your_table
    ADD COLUMN new_column integer;
ALTER TABLE your_table
    ALTER COLUMN new_column SET DEFAULT 0;

As this is basically only a (actually two) change to the catalog (no data change happens), it will complete pretty fast. Then depending on your needs and table usage, you can update the new column to the default in one step or in batches, and when finished, set the column to NOT NULL.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.