BUG #15915: ALTER COLUMN SET DATA TYPE fails with index already exists

Started by PG Bug reporting formover 6 years ago2 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: PG Bug reporting form (#1)
Re: BUG #15915: ALTER COLUMN SET DATA TYPE fails with index already exists

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&amp;a=commitdiff&amp;h=da1041fc3

At least, your example doesn't fail for me on 9.6 branch tip.

regards, tom lane