Re: Update inside (Insert) Trigger and Unique constraint... - Mailing list pgsql-general

From Richard Huxton
Subject Re: Update inside (Insert) Trigger and Unique constraint...
Date
Msg-id 200401220140.07382.dev@archonet.com
Whole thread Raw
In response to Re: Update inside (Insert) Trigger and Unique constraint...  ("D. Dante Lorenso" <dante@lorenso.com>)
List pgsql-general
On Wednesday 21 January 2004 20:12, D. Dante Lorenso wrote:
> NEVERMIND...  This is not a trigger problem.  It's a unique
> constraint problem...  If I have a unique constraint on
> a column like 'afile_version', and want to do an update on
> that column to add one to each number, is there a way to
> add an 'ORDER BY' to the update?
[snip]
>     FOR my_rec IN
>         SELECT afile_id
>         FROM audio_file
>         ORDER BY afile_version DESC
>     LOOP
>         /* roll back the version... */
>         UPDATE audio_file SET
>             afile_version = afile_version + 1
>         WHERE afile_id = my_rec.afile_id;
>     END LOOP;

This was mentioned in the last couple of weeks on one of the lists - don't
know which. Someone suggested doing UPDATE ...version=-version followed by
UPDATE ...version=(-version)+1

> And that does the trick, but I guess I might also be able
> to do something like this?:

Nope - or rather, if it does work I think it's down to chance.

>     UPDATE audio_file SET
>         afile_version = afile_version + 1
>     WHERE afile_id IN (
>         SELECT afile_id
>         FROM audio_file
>         ORDER BY afile_version DESC
>     );

PS - this is really a bug, but it doesn't seem to bite very often, and there
are work-arounds, so it hasn't reached the top of any developer's list yet.

--
  Richard Huxton
  Archonet Ltd

pgsql-general by date:

Previous
From: Kragen Sitaker
Date:
Subject: Re: varchar_pattern_ops in 7.3.4?
Next
From: Richard Huxton
Date:
Subject: Re: Lost plpgsql function