1252748 1252748 - 1 month ago 17
SQL Question

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!

Answer
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;

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;