Re: Unique indexes and updates

On Tue, Mar 11, 2003 at 12:24:40PM -0500, Zachary Beane wrote:
> 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?

Oops, the table definition should be, of course:

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

Any takers?