Menu
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.
805
In PostgreSQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement. You use the ALTER TABLE statement in PostgreSQL to add or drop a primary key.
> that table. Yes. By default, Postgres creates an "Object ID" for each row.
Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields.
The Postgres IF NOT EXISTS syntaxFirst, we specify the name of the table to which we want to add a column. We supply the IF NOT EXISTS option after the ADD COLUMN clause, and then we specify the name of the column and its data type.
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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
