CODEWITHSUNDEEP
sqlpostgresql
Laurent
Laurent

Reputation: 2168

Syntax error with if column on exist with primary key

I want to add a column which's also a primary key if it doesn't already exist on the table. If I do a simple

ALTER TABLE webinars_identities ADD COLUMN IF NOT EXISTS id uuid

It will work but if I do

ALTER TABLE webinars_identities ADD COLUMN IF NOT EXISTS id uuid PRIMARY KEY DEFAULT uuid_generate_v4();

It says it skips the alter table, but for some reason crashes right after:

NOTICE:  column "id" of relation "webinars_identities" already exists, skipping
ERROR:  multiple primary keys for table "webinars_identities" are not allowed

My original working query was

ALTER TABLE webinars_identities id uuid PRIMARY KEY DEFAULT uuid_generate_v4();

But this is not repeatable without error.

What am I doing wrong here ?

Upvotes: 1

Views: 1864

Answers (3)

user64204
user64204

Reputation: 287

It's a bug in postgres https://www.postgresql.org/message-id/13277.1566920001%40sss.pgh.pa.us

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.

Will be fixed in v13.0, hopefully.

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Handle it using duplicate_column exception and issue a notice, because someone rightly said that errors should never pass silently. 

DO $body$ 
 BEGIN 
    ALTER TABLE atable ADD COLUMN  id int primary key; --DEFAULT uuid_generate_v4()
 EXCEPTION
 WHEN duplicate_column THEN

  RAISE NOTICE 'ERROR: %,%',SQLSTATE,SQLERRM;

END $body$;

This will work the first time and does not fail on all following attempts, but gives you a message. All other errors if found in your statement will be raised as exceptions.

NOTICE:  ERROR: 42701,column "id" of relation "atable" already exists
DO

Upvotes: 1

Gurmokh
Gurmokh

Reputation: 2091

Try this. 

DO $$ BEGIN TABLE atable ADD COLUMN IF NOT EXISTS id int primary key ; exception when others then null ; END$$;

Upvotes: 0

Related Questions