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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general