Unique indexes and updates

From: Zachary Beane <xach(at)xach(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unique indexes and updates
Date: 2003-03-11 17:24:40
Message-ID: 20030311172439.GA3396@xach.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'd like to update a set of usernames that fit a certain criteria, but
I'm running into trouble with a unique index.

Here's a simplified way to reproduce my trouble:

create table users (
id int primary key,
username varchar(15)
);

insert into users (id, username) values (1, 'xach');
insert into users (id, username) values (2, '^xach');

Now to update them:

update users
set username = '^' || username
where trim(leading '^' from username) = 'xach';

This query would result in a consistent state (i.e. no conflict with
the unique index), but PostgreSQL rejects it with this:

ERROR: Cannot insert a duplicate key into unique index
users_username_key

Is there any way to get the effect I want with a single update?

Zach

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Partho Bhowmick 2003-03-11 17:31:54 Largest filesize under Linux
Previous Message Delao, Darryl W 2003-03-11 17:15:21 Re: General Performance questions