Re: implementing (something like) UNIQUE constraint using PL/pgSQL

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.