[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] implementing (something like) UNIQUE constraint using PL/pgSQL
From:       Michael Glaesemann <grzm () seespotcode ! net>
Date:       2007-01-27 11:58:24
Message-ID: 51AAD09D-F90D-480F-A93E-0C57246415D7 () seespotcode ! net
[Download RAW message or body]


On Jan 27, 2007, at 16:55 , Bruno Wolff III wrote:

> 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.

This may have already been mentioned upthread, but this sounds like a  
special case of the SQL queues I saw on Greg Sabino Mullane's blog:

http://people.planetpostgresql.org/greg/index.php?/archives/89- 
Implementing-a-queue-in-SQL-Postgres-version.html

Perhaps you could tweak that to serve your needs.

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
[prev in list] [next in list] [prev in thread] [next in thread]