CODEWITHSUNDEEP
sqlpostgresqlcheck-constraints
Luffydude
Luffydude

Reputation: 772

Check constraint for a condition in Postgresql

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

Upvotes: 3

Views: 5054

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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

Upvotes: 5

Related Questions