Is there a way to make a constraint that checks if, between a range, we have all the values of that-CodePudding

I was wondering if there was any way to check if, in a column, there were all values between a range. Example: i have an INTEGER column with values

i want to check if between 0 and 6 i have all values. (false in this example)

I think a solution might be: MAX(Column)-MIN(Column) 1 and the result has to be equal to COUNT(Column) but i'm not sure how to write it as a CONSTRAINT.

CodePudding user response：

you can use {select * from table where(column BETWEEN 0 AND 10)} 0 and 10 can be replaced with any numerical value you want

CodePudding user response：

To enforce this you would need a TRIGGER. A trigger can run before or after you insert or delete rows and can run for every row or for each statement. If you are only inserting a single row at a time then both are equivalent. If you might insert multiple rows you probably want a trigger that runs AFTER the STATEMENT is executed.

The trigger should run the check you described and should raise an exception if the condition is not met. There is an example of constructing a trigger in the manual (links below).

You may find it helpful to write two functions - one which checks the table returns true/false depending on if it is acceptable, and then a trigger function that calls that. This makes it easier to test your condition logic.