CODEWITHSUNDEEP
postgresqlalter
virnovus
virnovus

Reputation: 139

Altering a Postgres integer column to type boolean

I've recently been optimizing some of our Postgres tables by converting more complex data types to simpler ones where possible. In every case except one so far, this has been fairly straightforward, for instance:

ALTER TABLE products ALTER COLUMN price TYPE integer USING price::integer;

For converting text into custom enumerated data types, this has also been simple enough. I just wrote a PLPGSQL function that would convert text to the enum, then converted the column like so:

ALTER TABLE products ALTER COLUMN color TYPE color_enum USING text_to_color_enum(color);

This syntax fails though, in cases where I have to convert an integer to a boolean. These all fail:

ALTER TABLE products ALTER return_policy TYPE boolean USING return_policy > 0;

ALTER TABLE products ALTER return_policy TYPE boolean USING bool(return_policy);

ALTER TABLE products ALTER COLUMN return_policy TYPE boolean USING bool(return_policy);

ALTER TABLE products ALTER COLUMN return_policy TYPE boolean USING CASE WHEN return_policy <> 0 THEN TRUE ELSE FALSE END;

The error message is always the same:

ERROR: operator does not exist: boolean = integer

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

There are no null values in that column. All values are either zero or positive. SELECT pg_typeof(return_policy) FROM products LIMIT 1; returns integer. Creating a custom cast from integer to boolean fails, because apparently one already exists. The same thing happen in Postgres 9.4 and 9.5. What am I doing wrong here?

Upvotes: 4

Views: 5073

Answers (2)

virnovus
virnovus

Reputation: 139

One of my partial indexes had a condition WHERE return_policy = 30. (This number is meant to be the number of days the return policy is, but since we're giving everything either no return policy or a 30-day return policy, it doesn't make sense for it to be an int anymore.) Dropping the index allowed my original SQL code to run correctly.

Upvotes: 1

monteirobrena
monteirobrena

Reputation: 2620

Verify if the column is a constraint, if yes you need to remove the constraint before change the type.

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
ALTER TABLE products ALTER price TYPE bool USING CASE WHEN price = 0 THEN FALSE ELSE TRUE END;
ALTER TABLE products ALTER COLUMN price SET DEFAULT FALSE;

Upvotes: 6

Related Questions