BUG #15981: Alter table add column if not exists with constraint fails on constraint
The following bug has been logged on the website:
Bug reference: 15981
Logged by: Erika ONeal
Email address: e.oneal89@gmail.com
PostgreSQL version: 11.5
Operating system: Debian
Description:
SQL to reproduce:
`CREATE TABLE test(id uuid PRIMARY KEY);
ALTER TABLE test ADD IF NOT EXISTS id uuid PRIMARY KEY;`
Output: `NOTICE: column "id" of relation "test" already exists, skipping
ERROR: multiple primary keys for table "test" are not allowed`
Expected Outcome: If the column already exists, it should not attempt to
index it with the column constraint. Or if that is the correct behavior, the
documentation does not reflect that.
PG Bug reporting form <noreply@postgresql.org> writes:
CREATE TABLE test(id uuid PRIMARY KEY);
ALTER TABLE test ADD IF NOT EXISTS id uuid PRIMARY KEY;
Output: `NOTICE: column "id" of relation "test" already exists, skipping
ERROR: multiple primary keys for table "test" are not allowed`
Yeah, this is a well-known issue: the IF NOT EXISTS only conditionalizes
creation of the column, but it's reasonable to expect that it should
conditionalize creation of the index as well. There's work afoot to
improve this [1]/messages/by-id/10365.1558909428@sss.pgh.pa.us, but it's not moving very fast; at the earliest you
might see it fixed in v13.
regards, tom lane