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@xxxxxxxxxxxxx> wrote:
richard coleman <rcoleman.ascentgl@xxxxxxxxx> 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
