From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | amaury(at)castordoc(dot)com |
Subject: | BUG #17271: Updating enum columns type fails when constraints exist |
Date: | 2021-11-04 13:42:00 |
Message-ID: | 17271-8b4317357c4991e2@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17271
Logged by: Amaury Dumoulin
Email address: amaury(at)castordoc(dot)com
PostgreSQL version: 12.8
Operating system: alpine 3.14 on docker (host macOs 11.6)
Description:
Error message upon failure
The following fails
CREATE TYPE direction_state AS ENUM('UP', 'DOWN');
CREATE TABLE example (id SERIAL PRIMARY KEY, direction direction_state NOT
NULL, below BOOLEAN NOT NULL);
ALTER TABLE example ADD CONSTRAINT ck_example_direction CHECK ((direction =
'DOWN' AND below) OR (NOT below));
ALTER TYPE direction_state RENAME TO direction_state_old;
CREATE TYPE direction_state AS ENUM('UP', 'DOWN', 'UNKNOWN');
ALTER TABLE example ALTER COLUMN direction TYPE direction_state USING
direction::text::direction_state;
DROP TYPE direction_state_old
With a sibyllin error message
ERROR: operator does not exist: direction_state = direction_state_old
HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
If we drop and recreate the constraint it works
CREATE TYPE direction_state AS ENUM('UP', 'DOWN');
CREATE TABLE example (id SERIAL PRIMARY KEY, direction direction_state NOT
NULL, below BOOLEAN NOT NULL);
ALTER TABLE example ADD CONSTRAINT ck_example_direction CHECK ((direction =
'DOWN' AND below) OR (NOT below));
ALTER TYPE direction_state RENAME TO direction_state_old;
CREATE TYPE direction_state AS ENUM('UP', 'DOWN', 'UNKNOWN');
ALTER TABLE example DROP CONSTRAINT ck_example_direction;
ALTER TABLE example ALTER COLUMN direction TYPE direction_state USING
direction::text::direction_state;
DROP TYPE direction_state_old
ALTER TABLE example ADD CONSTRAINT ck_example_direction CHECK ((direction =
'DOWN' AND below) OR (NOT below));
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-11-04 13:58:04 | Re: BUG #17271: Updating enum columns type fails when constraints exist |
Previous Message | Peter Geoghegan | 2021-11-04 01:21:02 | Re: ERROR: posting list tuple with 20 items cannot be split at offset 168 |