CODEWITHSUNDEEP
sqlpostgresql
1252748
1252748

Reputation: 15362

postgresql - add boolean column to table set default

Is this proper postgresql syntax to add a column to a table with a default value of false

ALTER TABLE users
ADD "priv_user" BIT
ALTER priv_user SET DEFAULT '0'

Thanks!

Upvotes: 285

Views: 420734

Answers (5)

Eelke
Eelke

Reputation: 21993

ALTER TABLE users
  ADD COLUMN "priv_user" BOOLEAN DEFAULT FALSE;

you can also directly specify NOT NULL

ALTER TABLE users
  ADD COLUMN "priv_user" BOOLEAN NOT NULL DEFAULT FALSE;

UPDATE: following is only true for versions before postgresql 11.

As Craig mentioned on filled tables it is more efficient to split it into steps:

ALTER TABLE users ADD COLUMN priv_user BOOLEAN;
UPDATE users SET priv_user = 'f';
ALTER TABLE users ALTER COLUMN priv_user SET NOT NULL;
ALTER TABLE users ALTER COLUMN priv_user SET DEFAULT FALSE;

Upvotes: 516

Meet Patel
Meet Patel

Reputation: 510

In psql alter column query syntax like this 

Alter table users add column priv_user boolean default false ;

boolean value (true-false) save in DB like (t-f) value .

Upvotes: 9

LondonRob
LondonRob

Reputation: 78683

Just for future reference, if you already have a boolean column and you just want to add a default do:

ALTER TABLE users
  ALTER COLUMN priv_user SET DEFAULT false;

Upvotes: 20

Faisal Chohan
Faisal Chohan

Reputation: 61

If you are using postgresql then you have to use column type BOOLEAN in lower case as boolean.

ALTER TABLE users ADD "priv_user" boolean DEFAULT false;

Upvotes: 6

rfusca
rfusca

Reputation: 7705

If you want an actual boolean column:

ALTER TABLE users ADD "priv_user" boolean DEFAULT false;

Upvotes: 28

Related Questions