I've a field that is INTEGER NOT NULL DEFAULT 0 and I need to change that to bool.
This is what I am using:
ALTER TABLE mytabe
ALTER mycolumn TYPE bool
USING
CASE
WHEN 0 THEN FALSE
ELSE TRUE
END;
But I am getting:
ERROR: argument of CASE/WHEN must be type boolean, not type integer
********** Error **********
ERROR: argument of CASE/WHEN must be type boolean, not type integer
SQL state: 42804
Any idea?
Thanks.
Try this:
ALTER TABLE mytabe ALTER COLUMN mycolumn DROP DEFAULT;
ALTER TABLE mytabe ALTER mycolumn TYPE bool USING CASE WHEN mycolumn=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mytabe ALTER COLUMN mycolumn SET DEFAULT FALSE;
You need to remove the constraint first (as its not a boolean), and secondly your CASE statement was syntactically wrong.
Postgres can automatically cast integer to boolean. The key phrase is
using some_col_name::boolean
-- here some_col_name is the column you want to do type change
Above Answer is correct that helped me Just one modification instead of case I used type casting
ALTER TABLE mytabe ALTER COLUMN mycolumn DROP DEFAULT;
ALTER TABLE mytabe ALTER mycolumn TYPE bool USING mycolumn::boolean;
ALTER TABLE mytabe ALTER COLUMN mycolumn SET DEFAULT FALSE;
cannot cast type smallint to boolean, for example.
USING mycolumn::text::boolean;Also check that you don't have any CHECK constraint on you column like:
[...] CONSTRAINT blabla CHECK ((field = ANY (ARRAY[0, 1])))
otherwise you alter command will error with a cannot convert to boolean type
