Postgres ALTER TABLE ADD CONSTRAINT IF NOT EXISTS not working

Question

I need to add a Constraint if not exists and am hitting the following error. Note that a similar if not exists for a new Column, right above it, does work. There's some syntax error when adding a Constraint, am I missing something?

alter table requests_t
add constraint if not exists 
valid_bias_check CHECK (bias_flag::text = ANY (ARRAY['Y'::character varying::text, 'N'::character varying::text]));

Error

ERROR:  syntax error at or near "not"
LINE 2: add constraint if not exists

The answer is simple: as documented in the manual there is no option if not exists when using add constraint — user330315
– user330315, Commented Mar 18, 2022 at 14:12
Also, why is the following answer provided (and accepted)? stackoverflow.com/a/43755195/1005607 — gene b.
– gene b., Commented Mar 18, 2022 at 14:20

gene b. · Accepted Answer · 2022-03-18 14:33:19Z

5

Since Postgres doesn't support this syntax with constraints (see a_horse_with_no_name's comment), I rewrote it as:

alter table requests_t
drop constraint if exists valid_bias_check;

alter table requests_t
add constraint 
valid_bias_check CHECK (bias_flag::text = ANY (ARRAY['Y'::character varying::text, 'N'::character varying::text]));

answered Mar 18, 2022 at 14:33

gene b.

12.6k31 gold badges142 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres ALTER TABLE ADD CONSTRAINT IF NOT EXISTS not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related