Unique indexes and updates

Поиск
Список
Период
Сортировка
От Zachary Beane
Тема Unique indexes and updates
Дата
Msg-id 20030311172439.GA3396@xach.com
обсуждение исходный текст
Ответы Re: Unique indexes and updates  (Zachary Beane <xach@xach.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Yohans Mendoza
Дата:
Сообщение: unsubscribe
Следующее
От: "Delao, Darryl W"
Дата:
Сообщение: Re: General Performance questions