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!
ALTER TABLE table_name ALTER COLUMN col_name SET NOT NULL; Or you can put them all together in a single statement: ALTER TABLE table_name ADD COLUMN “col_name” BOOLEAN DEFAULT FALSE; This way it might take longer if the operation is huge.
Changing a Column's Default Value. To set a new default for a column, use a command like: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; Note that this doesn't affect any existing rows in the table, it just changes the default for future INSERT commands.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With