15
ALTER TABLE products ALTER COLUMN power_price DROP DEFAULT;
ALTER TABLE products ALTER COLUMN power_price TYPE bool USING (power_price::boolean);
ALTER TABLE products ALTER COLUMN power_price SET NOT NULL;
ALTER TABLE products ALTER COLUMN power_price SET DEFAULT false;

Postgres gives me this error:

Query failed: ERROR: cannot cast type numeric to boolean

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
FlyingNimbus
  • 453
  • 2
  • 5
  • 11

1 Answers1

47

Use:

ALTER TABLE products ALTER power_price TYPE bool USING (power_price::int::bool);

There is no direct cast defined between numeric and boolean. You can use integer as intermediate step. text would be another candidate since every type can be cast from / to text. Values have to be 1 / 0 for the text route, of course.

Better yet, do it all in a single command for better performance and shorter lock time:

ALTER TABLE products
  ALTER power_price DROP DEFAULT
, ALTER power_price TYPE bool USING (power_price::int::bool)
, ALTER power_price SET NOT NULL
, ALTER power_price SET DEFAULT false;

Details in the manual about ALTER TABLE.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Note on "values have to be 1 / 0 of course." Actually values can be any. 0 will be false and any other will be true. – Mahou5 Nov 29 '17 at 15:47
  • 1
    select 12 :: INT :: BOOLEAN – Mahou5 Nov 29 '17 at 15:49
  • @Mahou5: My quoted statement only applies to `text` as stepping stone. Consider: `SELECT 12::text::bool;` -> `ERROR: invalid input syntax for type boolean: "12"`. So one can pick whether to be strict in the cast ... – Erwin Brandstetter Nov 03 '18 at 14:53
  • In that you're right. I was only pointing that out based on the use of 'INT' as middle ground. – Mahou5 Nov 19 '18 at 13:09