Add a new column to PostgreSQL database, set initial value and set NOT NULL all in one go

Question

Using Django, I often have to add new fields to our PostgreSQL database. The fields usually have an initial value and they must be set to NOT NULL. My procedure for this involves three steps:

1) Add field and set initial value:

ALTER TABLE my_table ADD COLUMN my_field boolean;
UPDATE my_table SET my_field = FALSE;

2) Set NOT NULL:

ALTER TABLE my_table ALTER COLUMN my_field SET NOT NULL;

All three queries cannot be run in one go, because this results in a PostgreSQL error.

What's the most efficient way of performing this task? I'd like to do this in a single query - and with adding several fields in one go. Something along those lines:

ALTER TABLE my_table ADD COLUMN my_field boolean INITIAL FALSE SET NOT NULL, ADD COLUMN my_field2 boolean INITIAL FALSE SET NOT NULL;

Answer 1

Did you try this;

alter table my_table add column my_field1 boolean default false NOT NULL, 
                     add column my_field2 boolean default false NOT NULL;

Answer 2

You should do that by two steps:

1. Add column
2. Alter column: set constraint, add default

Query:

ALTER TABLE my_table ADD COLUMN my_field boolean;
ALTER TABLE my_table ALTER COLUMN my_field SET NOT NULL,
                     ALTER COLUMN my_field TYPE boolean USING false;

Or this:

ALTER TABLE my_table ADD COLUMN my_field boolean;
ALTER TABLE my_table ALTER COLUMN my_field SET NOT NULL,
                     ALTER COLUMN my_field SET DEFAULT false;

The benefit of first is that you can calculate value for each row based on your row data (in other words you can refer fields). for DEFAULT clause you must use bare value.

See documentation examples with timestamps