0

I have implemented this code to add a check constraint after creating the table

ALTER TABLE recruitment_data.candidate_experiences 
  ADD CONSTRAINT chk_experience_dates 
    CHECK (   experience_end_date IS NULL 
           OR experience_end_date >= experience_start_date);

but it gives an error when I run it more than once:

ERROR: constraint "chk_experience_dates" for relation "candidate_experiences" already exists

Since it is impossible to run ADD CONSTRAINT IF NOT EXISTS, is there any other way to run it twice without creating duplicate constraints, and without having to catch and handle or ignore the error?

What should I do to make this code re-runnable?

Improve this question
5
  • 1
    Please add you Postgres version, the definition of the table in question and some example insert statements, demonstrating which are allowed and which this constraint is supposed to prevent. What you're showing is perfectly valid but your mention of if not exists suggests you want this constraint to be able to compare against other rows, which a check isn't supposed to do. My guess is you want some sort of exclude or unique..without overlaps. Commented 18 hours ago
  • 1
    If you're getting an error, attach the full error message and the code that caused it. Commented 18 hours ago
  • 1
    18.0 is the version and error message is ERROR: constraint "chk_experience_dates" for relation "candidate_experiences" already exists. It is adding the constraint when this code is executed for the first time, but if I try to execute it one more time, it gives error that I have written above. Since it is impossible to write ADD CONSTRAINT IF NOT EXISTS, is there any other way to run it twice without producing duplicate or error that it already exists? Commented 18 hours ago
  • 1
    Do you want it to drop and recreate, or to just ignore if it exists (regardless of what the constraint predicate is)? Commented 17 hours ago
  • I would like to ignore if it already exists Commented 17 hours ago

1 Answer 1

Reset to default
0

You're correct that ALTER TABLE..ADD CONSTRAINT doesn't support an IF NOT EXISTS clause. But, ALTER TABLE:

  • allows you to combine multiple ALTER statements into one:

    All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into a list of multiple alterations to be applied together.

  • offers an opposite statement DROP CONSTRAINT IF EXISTS, that does support this clause

demo at db<>fiddle

ALTER TABLE recruitment_data.candidate_experiences 
  DROP CONSTRAINT IF EXISTS chk_experience_dates,
  ADD CONSTRAINT chk_experience_dates 
    CHECK (   experience_end_date IS NULL 
           OR experience_end_date >= experience_start_date);
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you, and what should I do to ignore the existence only? I mean the code will add the constraint if it does not exist but if it already exists, it will ignore it and do nothing instead of giving error.
The clause isn't supported so you'd have to use dynamic PL/pgSQL block, as shown in the threads @Charlieface found and liked to this one. Plain SQL won't let you conditionally execute an alter table based the contents of pg_constraint.
I understand, thank you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.