From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Carboni <stephen.entropy@gmail.com>
cc: pgsql-novice@postgresql.org
Subject: Re: Why is there no ADD CONSTRAINT IF NOT EXISTS ?
In-reply-to: 
 <CAN-rLW_L0s-nt5AC5A=RLtCjeB-aaZRd-oRfkKf0OeYXCNzHng@mail.gmail.com>
References: 
 <CAN-rLW_L0s-nt5AC5A=RLtCjeB-aaZRd-oRfkKf0OeYXCNzHng@mail.gmail.com>
Comments: In-reply-to Stephen Carboni <stephen.entropy@gmail.com>
	message dated "Wed, 21 Apr 2021 13:13:19 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <757477.1619014080.1@sss.pgh.pa.us>
Date: Wed, 21 Apr 2021 10:08:00 -0400
Message-ID: <757478.1619014080@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/757478.1619014080%40sss.pgh.pa.us>
Precedence: bulk

Stephen Carboni <stephen.entropy@gmail.com> writes:
> I can drop a constraint if it exists, but cannot simply add one if it does
> not exist. Is there something especially difficult about supporting this?

Probably some combination of (1) nobody got around to it yet and
(2) it's not entirely clear what the semantics ought to be.
Should we match on name, or contents of the constraint, and how
picky would a content match be?

There's a general problem with CREATE IF NOT EXISTS that you don't
really have any guarantee afterwards as to what are the details
of the object's definition.  Personally I prefer CREATE OR REPLACE
semantics, where you do know what the object looks like afterwards.
However, C.O.R. doesn't work that well unless there's a very clear
unique identity (e.g. name) for the object.  Since constraints are
often made without specifying a name, it's hard to be sure what
you're supposed to replace.  I'm not sure whether C.I.N.E. would
have as bad of a problem with that, but it seems possible.

			regards, tom lane