Thread: Constraint allowing value up to 2 times but no more than 2 times

On 10/12/15 12:04 PM, Dara Unglaube wrote:
> Greeetings.
> I'm trying to come up with a way to allow one column to have the same
> value up to two times, but no more than two times. I attempted adding a
> constraint to check the count of the value in the field  - count
> (trsqqq) <=2 but aggregate functions are not allowed in constraints. Is
> there another way to do this? Any help would be greatly appreciated.

The safest bet is some kind of unique constraint. That would require
having a separate count field, which you would limit to being 1 or 2 via
a CHECK constraint. You could use a trigger to set the value based on
what's already in the table.

By the way, the issue with doing a simple count is that it's not safe
from race conditions, like an insert and a delete happening together.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 10/12/15 1:41 PM, Dara Unglaube wrote:
> I created a view with the column of interest and a column of the count.
> How do I do a check constraint on a view or do it all at once as a
> subquery? Could you provide an example of how to create?

Please don't top-post. And do include the mailing list so others can learn.

The constraint would go on the table, not the view. The column would
need to not be a count, but a 'record number' or something similar. So
you'd have one record with 'record_number=1' and the second with
'record_number=2'.

CREATE TABLE ...(
   ...
   , record_number smallint NOT NULL CONSTRAINT
record_number_must_be_1_or_2 CHECK( record_number BETWEEN 1 AND 2 )
);
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com