From: Dmitry Tkach <dmitry@openratings.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: unique value - trigger?
Date: Thu, 17 Jul 2003 10:34:22 -0400
Message-ID: <3F16B3EE.2060006@openratings.com> (raw)
In-Reply-To: <200307171256.52098.gary.stainburn@ringways.co.uk>
References: <200307171256.52098.gary.stainburn@ringways.co.uk>
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
>
>
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: dmitry@openratings.com, gary.stainburn@ringways.co.uk
Subject: Re: unique value - trigger?
In-Reply-To: <3F16B3EE.2060006@openratings.com>
* 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