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

sql postgresql primary-key alter-table postgresql-9.1
Pavel S. picture Pavel S. · Mar 28, 2012 · Viewed 29.2k times · Source

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

a_horse_with_no_name picture a_horse_with_no_name · Mar 28, 2012

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More