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
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 |