Thread: BUG #15981: Alter table add column if not exists with constraint fails on constraint
BUG #15981: Alter table add column if not exists with constraint fails on constraint
From
PG Bug reporting form
Date:
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.
Re: BUG #15981: Alter table add column if not exists with constraint fails on constraint
From
Tom Lane
Date:
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], but it's not moving very fast; at the earliest you might see it fixed in v13. regards, tom lane [1] https://www.postgresql.org/message-id/flat/10365.1558909428%40sss.pgh.pa.us