Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot cast type numeric to boolean

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

like image 335
FlyingNimbus Avatar asked Oct 10 '13 08:10

FlyingNimbus


1 Answers

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 middle-ground. text would be another candidate for middle ground, since every type can be cast from / to text. Values have to be 1 / 0 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.

like image 165
Erwin Brandstetter Avatar answered Sep 28 '22 17:09

Erwin Brandstetter