Pavel S. Pavel S. - 9 months ago 98
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.


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)