I'm trying to add a constraint to a Postgresql table and I can't find the right syntax.
In my table I have diferent columns: a date, a type (enum with 'up' and 'down') and a foreign id. What I want to do is restrict the amount of rows with type 'down' to be less or equal to the amount of 'up' that have the same foreign id and date. How should I do this?
What I have done by now is this: ALTER TABLE stocks ADD CONSTRAINT chk_up_down CHECK COUNT(*) AS COUNT FROM stocks GROUP BY date, foreign_id, type, but I don't know how to compare from there.
Thanks.
Add a comment
|
2 Answers
This is not possible with a CHECK contraint in PostgreSQL.
Doc says:
Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1). The system column tableoid may be referenced, but not any other system column.
But it should be possible with a trigger: read link added in comment by Laurenz Albe.
1 Comment
Laurenz Albe
But triggers are vulnerable to race conditions.
You can only do this with a check constraint if you add a helper function to return the difference in values. Then you can require that the difference be greater than or equal to zero.
Another method -- which requires a trigger -- is to store the net amount in another table, and update it when the data changes (inserts/updates/deletes). Then you can add a check constraint in that table to do what you want.
