Add primary key to PostgreSQL table only if it does not exist

Question

I have a simple table creating a script in Postgres 9.1. I need this to create a table with 2 PK attributes only if it does not exist.

CREATE TABLE IF NOT EXISTS "mail_app_recipients" ( "id_draft" Integer NOT NULL, "id_person" Integer NOT NULL ) WITH (OIDS=FALSE); -- this is OK ALTER TABLE "mail_app_recipients" ADD PRIMARY KEY IF NOT EXISTS ("id_draft","id_person"); -- this is problem since "IF NOT EXISTS" is not allowed. 

Any solution how to solve this problem? Thanks in advance.

Answer 1

Why not include a PK definition inside a CREATE TABLE:

 CREATE TABLE IF NOT EXISTS mail_app_recipients ( id_draft Integer NOT NULL, id_person Integer NOT NULL, constraint pk_mail_app_recipients primary key (id_draft, id_person) ) 

Answer 2

You can do something like the following, but it’s better to include it in the create table, as a_horse_with_no_name suggests.

 if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'table_name' and constraint_type = 'PRIMARY KEY') then ALTER TABLE table_name ADD PRIMARY KEY (id); end if; 

Answer 3

You can try DROP it before creating ( DROP has an IF EXISTS clause):

 ALTER TABLE mail_app_recipients DROP CONSTRAINT IF EXISTS mail_app_recipients_pkey; ALTER TABLE mail_app_recipients ADD CONSTRAINT mail_app_recipients_pkey PRIMARY KEY ("id_draft","id_person"); 

Note that this requires the mail_app_recipients_pkey name of the primary key constraint — in this example, mail_app_recipients_pkey .