From: Bruno Wolff III <bruno@wolff.to>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: implementing (something like) UNIQUE constraint using PL/pgSQL
Date: Sat, 27 Jan 2007 01:55:05 -0600
Message-ID: <20070127075505.GA8035@wolff.to> (raw)
In-Reply-To: <45B9CCC6.4000109@fuzzy.cz>
References: <45B9CCC6.4000109@fuzzy.cz>
On Fri, Jan 26, 2007 at 10:41:26 +0100,
Tomas Vondra <tv@fuzzy.cz> wrote:
>
> in our application we need to implement a constraint that enforces 'at
> most N rows with this value', that is we have a table with 'flag' column
> and for each value there should be at most 10 rows (for example, the
> exact number does not matter).
Another approach is to add a instance number column and constrain that
value to be between 1 and 10. And make value and instance number unique.
You'll need to do a bit more work when inserting new rows than normal
(to find a free instance number). This should be very robust against
getting in a bad state.
If you go with enforcing the condition with a trigger you need to be careful
about simultaneous inserts and visibility.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, tv@fuzzy.cz
Subject: Re: implementing (something like) UNIQUE constraint using PL/pgSQL
In-Reply-To: <20070127075505.GA8035@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox