CODEWITHSUNDEEP
postgresqlalter-table
Ken
Ken

Reputation: 144

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

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.

Upvotes: 1

Views: 3145

Answers (1)

Ken
Ken

Reputation: 144

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.

Upvotes: 2

Related Questions