Check constraint for a condition in Postgresql

Question

I'm trying to create a check constraint to prevent people from changing a sales_status to 3 unless the progression_status is 80.

I thought it was

ALTER TABLE mytable 
   ADD CONSTRAINT sales_status_cant_be_3_for_nonprogressed 
   CHECK (((sales_status = 3 ) or (progression_status < 80)))

however this is returning an error saying that some row violates it. When I run the query

select * from mytable where sales_status = 3 and progression_status < 80

I get no results as expected. Yet I can't seem to make the check constraint to work

I think constraint name is more than 32 characters. Preferably, read the variable naming rules. — Muhammad Waheed
– Muhammad Waheed, Commented Feb 1, 2019 at 11:07
@MuhammadWaheed: constraint names can be up to 63 characters in Postgres — user330315
– user330315, Commented Feb 1, 2019 at 11:59

Gordon Linoff · Accepted Answer · 2019-02-01 12:40:54Z

6

Presumably, the error is because you have existing data that violates the constraint. So, check if this is true in existing data:

select t.*
from mytable t
where not ( (sales_status = 3 ) or (progression_status < 80) );

Note that this assumes that the columns are not null. (check constraints and where treat null booleans differently.)

EDIT:

I think the logic you want is:

CHECK ((sales_status <> 3 ) or (progression_status >= 80));

answered Feb 1, 2019 at 11:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Answer 1