Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres ALTER TABLE ADD CONSTRAINT IF NOT EXISTS not working

Tags:

postgresql

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 

enter image description here

like image 231
gene b. Avatar asked Jun 20 '26 07:06

gene b.


1 Answers

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]));
like image 120
gene b. Avatar answered Jun 23 '26 03:06

gene b.



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!