Postgres Alter Column Integer to Boolean

Question

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.

Answer 1

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.