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

Question

I have simple table creating script in Postgres 9.1. I need it to create the table with 2-attributes PK 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.

score 50 · Answer 1 · answered Sep 07 '16 at 21:37

50

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;

answered Sep 07 '16 at 21:37

Tom Gerken

2,930
3
24
28

11

It's important to notice that this code should be executed within `DO $$ BEGIN ... END $$;`, [see](https://www.postgresql.org/docs/current/plpgsql-control-structures.html) – artaxerx May 29 '20 at 10:34

score 22 · Answer 2 · answered May 20 '19 at 14:32

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.

score 22 · Accepted Answer · answered Mar 28 '12 at 11:44

22

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)
)

answered Mar 28 '12 at 11:44

Thanks, that's what I was looking for. Separate ADD PRIMARY KEY IF NOT EXISTS is impossible? – Pavel S. Mar 28 '12 at 11:57
3

No, there is no `IF NOT EXISTS` option for the `ALTER TABLE` statement. – Mar 28 '12 at 11:59
cool stuff, helped me to this elegantly and idempotent with Ansible! – Markus Apr 02 '20 at 11:25

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

3 Answers3