Re: Strange inconsistency with UPDATE

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