X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id 7BE88CCA19
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 14:33:24 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 59383-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 14:33:47 +0000 (GMT)
Received: from exchange.hq.openratings.com (unknown [141.149.165.226])
	by svr1.postgresql.org (Postfix) with ESMTP id 2A940CCA08
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 11:33:22 -0300 (ADT)
Received: from openratings.com (cancun.hq.openratings.com [10.0.0.225]) by
	exchange.hq.openratings.com with SMTP (Microsoft Exchange
	Internet Mail Service Version 5.5.2653.13)
	id 38QM44C4; Thu, 17 Jul 2003 10:33:51 -0400
Message-ID: <3F16B3EE.2060006@openratings.com>
Date: Thu, 17 Jul 2003 10:34:22 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: unique value - trigger?
References: <200307171256.52098.gary.stainburn@ringways.co.uk>
In-Reply-To: <200307171256.52098.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/221
X-Sequence-Number: 14335

Gary Stainburn wrote:

>Hi folks,
>
>I'm back with my lnumbers table again.
>
>nymr=# \d lnumbers
>               Table "lnumbers"
>  Column   |         Type          | Modifiers
>-----------+-----------------------+-----------
> lnid      | integer               | not null
> lnumber   | character varying(10) | not null
> lncurrent | boolean               |
>Primary key: lnumbers_pkey
>Triggers: RI_ConstraintTrigger_7575462
>
>While each loco can have a number of different numbers, only one can be 
>current at any one time.
>
>I want to make it so that if I set lncurrent to true for one row, any existing 
>true rows are set to false.
>
>I'm guessing that I need to create a trigger to be actioned after an insert or update which would update set lncurrent=false where lnid not = <current lnid> 
>

Why "not"? I thought, you wanted just the opposite - update the ones 
that *do* have the same lnid?
I'd also recommend you to add ' and lncurrent' to the query - otherwise 
every insert would be updating *every* row with the same lnid (it 
doesn't check if the new row is actually the same as the old one) before 
updating, and that may be expensive.
You may also want to create a pratial index on lnumbers (lnid) where 
lncurrent to speed up your trigger

>but I can't seem to sus it put.
>  
>
What is the problem?

Dima

>  
>