PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15915
PG Version9.6.14
OSCentos 7
Opened2019-07-18 03:51:52+00
Reported byGreg Amer
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15915
Logged by:          Greg Amer
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-07-18 03:51:52+00PG Bug reporting formBUG #15915: ALTER COLUMN SET DATA TYPE fails with index already exists
2019-07-18 03:57:03+00Tom LaneRe: BUG #15915: ALTER COLUMN SET DATA TYPE fails with index already exists