-1

I have implemented this code to add acheck 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 runADD CONSTRAINTIF 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 codere-runnable?

Dale K's user avatar
Dale K
28.1k15 gold badges59 silver badges85 bronze badges
askedNov 20 at 11:52
Beliz Yazici's user avatar
5
  • 1
    Please add you Postgres version, the definition of the table in question and some exampleinsert statements, demonstrating which are allowed and which this constraint is supposed to prevent. What you're showing is perfectly valid but your mention ofif not exists suggests you want this constraint to be able to compare against other rows, which acheck isn't supposed to do. My guess is you want some sort ofexclude orunique..without overlaps.CommentedNov 20 at 12:32
  • 1
    If you're getting an error, attach the full error message and the code that caused it.CommentedNov 20 at 12:37
  • 1
    18.0 is the version and error message isERROR: 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 writeADD CONSTRAINT IF NOT EXISTS, is there any other way to run it twice without producing duplicate or error that it already exists?CommentedNov 20 at 13:01
  • 1
    Do you want it to drop and recreate, or to just ignore if it exists (regardless of what the constraint predicate is)?CommentedNov 20 at 13:34
  • I would like to ignore if it already existsCommentedNov 20 at 14:06

1 Answer1

1

You're correct thatALTER TABLE..ADD CONSTRAINT doesn't support anIF NOT EXISTS clause. But,ALTER TABLE:

  • allows you to combine multipleALTER statements into one:

    All the forms ofALTER TABLE that act on a single table, exceptRENAME,SET SCHEMA,ATTACH PARTITION, andDETACH PARTITION can be combined into a list of multiple alterations to be applied together.

  • offers an opposite statementDROP 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);
answeredNov 20 at 13:30
Zegarek's user avatar
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 analter table based the contents ofpg_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.