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!
5 Answers
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;
7 Comments
Craig Ringer
Note that if your table is big this can take a long time and lock the table for the entire time. It's faster to split it into steps: add the column without a default with
ALTER TABLE users ADD COLUMN priv_user BOOLEAN;, then UPDATE users SET priv_user = 'f'; and finally if you need to ALTER TABLE users ALTER COLUMN priv_user SET NOT NULL;.
mrvisser
The approach that is split into steps is not adding a default value. Is it still faster to add a "DEFAULT 'f'" in a separate step?
Eelke
Yes adding the default in a seperate step is only a meta data operation and thus very fast.
Eelke
The actual rules are that keywords and unquoted identifiers are not case sensitive so BOOLEAN is allowed thought internally postgresql will use boolean. I tend to write standard types and SQL keywords in uppercase, I do use lowercase for all my own identifiers.
hallundbæk
@CraigRinger Shouldn't the performant case be:
ALTER TABLE users ADD COLUMN priv_user BOOLEAN; ALTER TABLE users ALTER COLUMN priv_user SET DEFAULT FALSE; UPDATE users SET priv_user = 'f'; ALTER TABLE users ALTER COLUMN priv_user SET NOT NULL; Otherwise there might come another row in between statement 2 and 3 which does not have the value, which would cause ALTER TABLE users ALTER COLUMN priv_user SET NOT NULL; to fail? |
If you want an actual boolean column:
ALTER TABLE users ADD "priv_user" boolean DEFAULT false;
1 Comment
Benjamin R
Note: not all versions of Postgres support this being defined in one line.
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;
2 Comments
CPS
Are there performance implications for this operation for large tables? Specifically changing a default value to another value without changing the type, on postgres 9.6
LondonRob
Good question @CPS. I don't know the answer but since this doesn't do anything to existing rows (it's not like a NOT NULL), this shouldn't take any time, regardless of the size of the table.
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 .
Comments
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;
Comments
booleancolumn?