Re: Why isn't there a IF NOT EXISTS clause on constraint creation?

Tom,
Thanks, but a CREATE OR REPLACE is functionally equivalent to a DROP IF NOT
EXISTS; ALTER TABLE pairing.
In both cases you would be recreating an identical constraint to the one
that exists if it was already there. If you have large tables, and/or a
composite key, that's a non-trivial amount of time and resources wasted.

Typically my case is that I have a large number of tables that have been
move/recreated from another source. Perhaps they we done at different
times, by different people, or as in my present case they were copied
programmatically and outside forces caused a random number of table to be
built without their indices and constraints. For the indices the
solution's trivial. I just rerun the script that creates the indices with
the IF NOT EXISTS clause in the CREATE INDEX command. Those that already
have the index are skipped, those that are missing it get it created. For
the primary keys, there's no such option. Other than writing my own
function to effectively wrap the existing ALTER TABLE command, I have to
either manually check the PKey status of hundreds of tables and then modify
the script that adds the PKeys, or rewrite the alter statement to take into
account that it might already exist, or just drop any that exist and add
them all back in again.

There are literally dozens of places in PostgreSQL where the inexplicable
lack of IF EXISTS / IF NOT EXISTS semantics causes untold manual rechecking
(trivial for a couple of tables not so much for hundreds or thousands of
tables), the writing of ad hoc PL/pgSQL DO blocks, or creating custom
functions whose sole purpose is to wrap the existing command in order to
add the missing IF EXISTS/IF NOT EXISTS functionality.

We can't be so afraid of a 'foot-gun' that we force users to go hunting big
game with a NERF gun instead.

I hope that helps explain things better,
rik.

On Wed, Aug 30, 2023 at 5:26 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)com> writes:
> > Why doesn't ALTER TABLE ... ADD CONSTRAINT not come with an ALTER TABLE
> > ... ADD CONSTRAINT IF NOT EXISTS variant?
>
> There's a general allergy in this project to CREATE IF NOT EXISTS,
> because it's a foot-gun. If the command succeeds, you know that
> an object (constraint or whatever) by that name exists, but you
> have no right to assume anything whatsoever about its properties.
>
> CREATE OR REPLACE semantics are a lot less squishy. Would a
> command of that form solve your problem? (I'm not sure about
> a pleasing way to write that within ALTER TABLE, but this gripe
> is about semantics not syntax.)
>
> regards, tom lane
>