Thread: BUG #15915: ALTER COLUMN SET DATA TYPE fails with index already exists
BUG #15915: ALTER COLUMN SET DATA TYPE fails with index already exists
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15915 Logged by: Greg Amer Email address: greg.amer@smxemail.com PostgreSQL version: 9.6.14 Operating system: Centos 7 Description: When attempting to set the data type on multiple columns in a single sql where the columns have indexes, the alter call fails. This previously worked on 9.6.6. Example: # CREATE TABLE a ( b VARCHAR(100), c VARCHAR(100)); CREATE TABLE # CREATE INDEX a_idx ON a(b); CREATE INDEX # CREATE INDEX a1_idx ON a(c); CREATE INDEX # INSERT INTO a VALUES ('Test', 'Test'); INSERT 0 1 # ALTER TABLE a ALTER b TYPE text, ALTER c TYPE text; ERROR: relation "a_idx" already exists This contradicts the documentation (and previous behaviour) "SET DATA TYPE This form changes the type of a column of a table. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression." Note this works if only altering a single column # ALTER TABLE a ALTER b TYPE text; ALTER TABLE
PG Bug reporting form <noreply@postgresql.org> writes: > When attempting to set the data type on multiple columns in a single sql > where the columns have indexes, the alter call fails. This previously worked > on 9.6.6. Thanks for the report! I believe this is the same issue already reported and fixed at https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=da1041fc3 At least, your example doesn't fail for me on 9.6 branch tip. regards, tom lane