Postgres - Alter Column: ERROR: operator does not exist: boolean = integer

Question

I have a database column (running) which is of type (int2) in postgres. It has data in it. The data in this column is either a 1 or a 0.

I want to alter the column to boolean. The reason i am here is because the query below worked on 2 previous test db's I with no issue. I cannot figure out why it fails on this new database.

Some background information: We are migrating from Oracle to Postgres. The database has the orifice plugin.

I have tried casting, but that fails.

This is the original working query, which doesn't work on the new db:

ALTER TABLE system_status ALTER running TYPE bool USING CASE WHEN running = 0 THEN FALSE ELSE TRUE END;

ERROR:
Query execution failed

Reason:
SQL Error [42883]: ERROR: operator does not exist: boolean = integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Sounds as if running already is a boolean column. Btw: you could simplify your expression to ... type bool using running = 1 — user330315
– user330315, Commented Jun 26, 2019 at 8:49
To add more detail, It's complaining that running = 0 comparison cannot be done as the = operator for comparing boolean and intergers do not exist, which indicates that running is already a boolean — Avin Kavish
– Avin Kavish, Commented Jun 26, 2019 at 8:57
Thanks for your input, i thought the same so i disconnected, reconnected, refreshed but it did reflect as being (int2). I have found the issue eventually, see my answer. — Ken
– Ken, Commented Jun 26, 2019 at 8:58

Ken · Accepted Answer · 2019-06-26 08:56:50Z

2

There was a constraint on the column, it had a different name to our previous database for some or other reason so when i ran the delete constraint script, it seemed like it did, i probably should have confirmed. The way we were doing the migration was copying prod to test and creating scripts based on that for our alters, but the latest db has a different constraint names so something must have been done differently when the new db was created.

So check if there are no constraints attached to the column if you have the same issue.

answered Jun 26, 2019 at 8:56

Ken

1442 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SuperPoney Over a year ago

Yes that's it, verify that you don't have a CHECK constraint on you column like : CONSTRAINT blabla CHECK ((field = ANY (ARRAY[0, 1])))

Collectives™ on Stack Overflow

Postgres - Alter Column: ERROR: operator does not exist: boolean = integer

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related