Add syntax for to use "IF NOT EXISTS" modifier to "ALTER TABLE add CONSTRAINT"

[glennfawcett]

To simplify pre-check coding, it would be great to have the "IF NOT EXISTS" modifier added. Consider the following example:

root@localhost:26257/defaultdb> CREATE TABLE feedback (id uuid primary key, hash string);
CREATE TABLE

Time: 9.601966ms

root@localhost:26257/defaultdb> ALTER TABLE feedback ADD CONSTRAINT feedback_hash_key UNIQUE (hash);
ALTER TABLE

Time: 166.699451ms

root@localhost:26257/defaultdb> ALTER TABLE feedback ADD CONSTRAINT feedback_hash_key UNIQUE (hash);
ERROR: duplicate constraint name: "feedback_hash_key"
SQLSTATE: 42710

Would like to have the following return without throwing an error:

ALTER TABLE feedback ADD CONSTRAINT IF NOT EXISTS feedback_hash_key UNIQUE (hash);

[awoods187]

@rohany have you looked at this recently in the context of some of your other work?

[rohany]

I haven't seen this before in PG. I'd imagine that they don't have an ifne clause for constraints because the ifne could make you think you added a constraint of a certain type, which a constraint of a different type satisfied the ifne which is unexpected behavior IMO.

[ajwerner]

Slack convo on the topic:
Andrew Werner:

Like, say the first one succeeds:
ALTER TABLE ... ADD CONSTRAINT IF NOT EXISTS c UNIQUE (a);
This second one should no-op:
ALTER TABLE ... ADD CONSTRAINT IF NOT EXISTS c UNIQUE (a);
What about?
ALTER TABLE ... ADD CONSTRAINT IF NOT EXISTS c CHECK (a > 1);
Or:
ALTER TABLE ... ADD CONSTRAINT IF NOT EXISTS c UNIQUE (a, b);
I can see an argument for failure if the constraint isn’t exactly what exists.

Peter Mattis:

What do we do for ALTER TABLE … ADD COLUMN IF NOT EXISTS when the column type or nullability are different?

Andrew Werner:

no-op I’m pretty sure. Same for CREATE TABLE IF NOT EXISTS with a totally different table definition.

Peter Mattis:

I’d stick with that behavior for constraints as well. No reason to make them special.

---

Seems like a strong vote in favor of adding it.

[petermattis]

The lack of ADD CONSTRAINT IF NOT EXISTS has causing a bit of headache with schema migrations in CC the past week. I suspect this isn't terribly difficult to implement, but would have an outsize impact on the quality of life for some CC engineers.