Pavel S. Pavel S. - 11 months ago 107
SQL Question

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

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.

Answer Source

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)