Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 283039FA6AF for ; Sat, 27 Jan 2007 03:55:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 72500-01 for ; Sat, 27 Jan 2007 03:55:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 2FF279FA371 for ; Sat, 27 Jan 2007 03:55:11 -0400 (AST) Received: (qmail 13464 invoked by uid 500); 27 Jan 2007 07:55:05 -0000 Date: Sat, 27 Jan 2007 01:55:05 -0600 From: Bruno Wolff III To: Tomas Vondra Cc: pgsql-sql@postgresql.org Subject: Re: implementing (something like) UNIQUE constraint using PL/pgSQL Message-ID: <20070127075505.GA8035@wolff.to> Mail-Followup-To: Bruno Wolff III , Tomas Vondra , pgsql-sql@postgresql.org References: <45B9CCC6.4000109@fuzzy.cz> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <45B9CCC6.4000109@fuzzy.cz> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/146 X-Sequence-Number: 27298 On Fri, Jan 26, 2007 at 10:41:26 +0100, Tomas Vondra 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.