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, Feb 01 '19 at 11:07
@MuhammadWaheed: constraint names can be up to 63 characters in Postgres — , Feb 01 '19 at 11:59

Gordon Linoff · Accepted Answer · 2019-02-01T12:40:54.910

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));

1 Answers1