63

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
samsina
  • 973
  • 2
  • 11
  • 16

3 Answers3

122

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.

catchdave
  • 9,053
  • 2
  • 27
  • 16
16

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;
Kemin Zhou
  • 6,264
  • 2
  • 48
  • 56
Rahul Shinde
  • 853
  • 9
  • 7
0

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

SuperPoney
  • 563
  • 6
  • 21