3

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?

virnovus
  • 129
  • 1
  • 12
  • 1
    See this http://stackoverflow.com/questions/1740303/postgres-alter-column-integer-to-boolean – monteirobrena Feb 04 '16 at 19:14
  • Yes, I read that page and tried those methods. They didn't work for me, and I continued to get the same error message as before. – virnovus Feb 04 '16 at 19:30

2 Answers2

5

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;
monteirobrena
  • 2,562
  • 1
  • 33
  • 45
  • There was originally a constraint, which gave me an error message stating that there was a constraint. I removed the constraint, and now I'm getting the current error message. Doing `\d+ products` reveals no modifiers on the `return_policy` column. – virnovus Feb 04 '16 at 19:29
  • @virnovus do you have a null value in this column? – monteirobrena Feb 04 '16 at 19:34
  • No. I checked for that already, and like I said, there are no null values in that column. – virnovus Feb 04 '16 at 19:35
  • 1
    @virnovus could you post the scheme of this table, please? Even the summary. – monteirobrena Feb 04 '16 at 19:37
  • And, there it is. Somehow I missed it the first million times I looked over the schema. Two partial indexes reference that column. Dropping the indexes and running it again made the original conversion code work correctly. Not the error message I would expect to get in this scenario, but at least I have my answer. – virnovus Feb 04 '16 at 19:50
1

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.

virnovus
  • 129
  • 1
  • 12