CODEWITHSUNDEEP
sqlpostgresqlcastingddlalter-table
FlyingNimbus
FlyingNimbus

Reputation: 445

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

Upvotes: 16

Views: 28214

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656824

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.

Upvotes: 48

Related Questions