Newbe Dev Stack

Answer : Is there a way to prevent a full table scan during the alter table statement? At this time there is no supported, safe way to do that with PostgreSQL. Some kind of ALTER TABLE ... ADD CONSTRAINT ... CONCURRENTLY would be nice, but nobody's implemented it. Same with the alternative of adding a NOT VALID constraint that still affects new rows, and that you then VALIDATE later - it'd be good, and it's something everyone knows is needed but nobody's had the time or funding to add yet. In theory you could directly modify the system catalogs to add the constraint if you know it is true and valid. In practice, well, it's generally not a great idea. So no, there isn't really a way. One potential alternative is to create a check constraint using NOT VALID , then validating the check constraint later. This method requires holding an ACCESS EXCLUSIVE lock only for the duration to create the constraint, which should be on the order of millisecon...

Answer : 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) ) 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; 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 th...