Re: Strange inconsistency with UPDATE
От | Michael Glaesemann |
---|---|
Тема | Re: Strange inconsistency with UPDATE |
Дата | |
Msg-id | E699987C-122A-41AB-A720-D3DE11EAE53E@seespotcode.net обсуждение исходный текст |
Ответ на | Strange inconsistency with UPDATE ("Phoenix Kiula" <phoenix.kiula@gmail.com>) |
Ответы |
Re: Strange inconsistency with UPDATE
("Phoenix Kiula" <phoenix.kiula@gmail.com>)
|
Список | pgsql-general |
On Aug 16, 2007, at 21:58 , Phoenix Kiula wrote: > However, I see some inconsisent behavior from Postgresql. When I issue > an UPDATE command , it shows me a duplicate violation (which could be > correct) -- > > -# update TABLE set ACOLUMN = lower(ACOLUMN); > ERROR: duplicate key violates unique constraint > "TABLE_ACOLUMN_key" > > So I try to find out the offending values of this ACOLUMN that become > duplicated when lower(ACOLUMN) is issued: > > -# SELECT lower(ACOLUMN), count(*) FROM TABLE > GROUP BY lower(ACOLUMN) HAVING count(*) > 1 ; > -------+------- > lower | count > -------+------- > (0 rows) > > But this doesn't make sense! If there are no columns that get > repeated, how can it violate the UNIQUE constraint? I suspect you're not showing us the exact queries you're running. For one, you can't have a table named TABLE (without quotes) in PostgreSQL. # create table TABLE (ACOLUMN text not null unique); ERROR: syntax error at or near "TABLE" LINE 1: create table TABLE (ACOLUMN text not null unique); Perhaps something else you changed when changing the table name, maybe to simplify the appearance of the query, affects the results you're seeing. Things appear to work as expected on my end. test=# select version(); version ------------------------------------------------------------------------ ---------------------------------------------------------------------- PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5367) (1 row) test=# create table strings (a_string text primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "strings_pkey" for table "strings" CREATE TABLE test=# insert into strings (a_string) values ('string'), ('STRING'), ('String'), ('number'); INSERT 0 4 test=# select * from strings; a_string ---------- string STRING String number (4 rows) test=# select lower(a_string), count(*) from strings group by lower (a_string); lower | count --------+------- string | 3 number | 1 (2 rows) test=# select lower(a_string), count(*) from strings group by lower (a_string) having count(*) > 1;; lower | count --------+------- string | 3 (1 row) test=# update strings set a_string = lower(a_string); ERROR: duplicate key violates unique constraint "strings_pkey" Another possibility is the setting of LC_COLLATE used during initdb, but I would think that lower() would be self-consistent under all collations. You might want to check if you're using a collation other than C though. test=# show lc_collate; lc_collate ------------ C (1 row) Hope this gives additional information to help you debug this. Michael Glaesemann grzm seespotcode net
В списке pgsql-general по дате отправления:
Следующее
От: Tyson Lloyd ThwaitesДата:
Сообщение: [RESEND] Transaction auto-abort causes grief with Spring Framework