Re: Update violating constraint

Alvaro Herrera wrote:
> Alban Hertroys wrote:
> > Richard Huxton wrote:
> > > Alban Hertroys wrote:
> > >> Naz Gassiep wrote:
> > >>> Hi,
> > >>> I'm trying to do an update on a table that has a unique constraint
> > >>> on the field, I need to update the table by setting field = field+1
> > >
> > >> I think you're looking for deferrable constraints; see:
> > >>
> > >> http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html
> > >
> > > Which won't work with unique constraints unfortunately. That's because
> > > they're implemented through a unique index.
> >
> > I appreciate the complexities involved, but that really ought to work on
> > a single statement. I recall seeing something along these lines on the
> > TODO list some time ago?
>
> It is still on the TODO list. If you want it to disappear from there,
> your best bet is implementing a fix, followed by motivating someone to
> do it for you. If you don't, bets are someone will do it eventually
> (which may be too late for your taste).

Yes, TODO has:

o Allow DEFERRABLE and end-of-statement UNIQUE constraints?

This would allow UPDATE tab SET col = col + 1 to work if col has
a unique index. Currently, uniqueness checks are done while the
command is being executed, rather than at the end of the statement
or transaction.
http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html
http://archives.postgresql.org/pgsql-hackers/2006-09/msg01458.php

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +