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;

Petar Butkovic · Accepted Answer · 2014-09-20 08:40:25Z

7

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;

answered Sep 20, 2014 at 8:40

Petar Butkovic

1,91016 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Simon Steinberger Over a year ago

Since Django doesn't set the DEFAULT value on a database level, I remove this in a second step. Although this sequence appears to be less efficient than my original one, I prefer your way, because I often have trouble assigning the NOT NULL flag right after creating a field on our heavily used database. New null values are added just too quickly ...

Mickael V. Over a year ago

Isn't this fundamentally different ? In the original way of the poster, the field gets an initial value, but then for the next inserts it will be mandatory to specify the field. Here you put a default value, meaning if the value is not specified for this field on insertion, false will be used

Rakshith Ravi Over a year ago

Is there a way to set the initial value for a new column without setting the default value?

Eugen Konkov · Accepted Answer · 2018-07-14 12:30:49Z

2

You should do that by two steps:

Add column
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

answered Jul 14, 2018 at 12:30

Eugen Konkov

26k17 gold badges128 silver badges192 bronze badges

Comments

Vivek S. · Accepted Answer · 2014-09-20 09:29:02Z

0

You can wrap your DDL into a fucntion that will be a good idea in your case, given below is a an example for your case.

Hope it works !!

CREATE OR REPLACE FUNCTION altertable(tablename text, colname text, coltype text,coldef text)
RETURNS void AS
$BODY$ 
BEGIN 
EXECUTE ' ALTER TABLE '|| quote_ident(tablename) || ' ADD COLUMN ' || quote_ident(colname) ||' '|| coltype ||' DEFAULT '||coldef||' NOT NULL'; END $BODY$
LANGUAGE plpgsql VOLATILE

Point Of Interest : able to alter any table,any column_type

Usage :

select altertable('tbl1','bool_col','boolean','true')
select altertable('tbl2','int_col','Integer','1')

answered Sep 20, 2014 at 9:29

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related