Add Primary Key To PostgreSQL Table Only If It Does Not Exist

Add Primary Key To PostgreSQL Table Only If It Does Not Exist

January 28, 2000

Answer :

Why not include the PK definition inside the 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) )

You could do something like the following, however it is 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;

You can try to DROP it before creating it (DROP has the 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 require that you give a name to the primary key constraint - in this example mail_app_recipients_pkey.

Comments