Re: Strange inconsistency with UPDATE

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/16/07 21:58, Phoenix Kiula wrote:
> I am trying to force a column to have lowercase because Postgresql is
> case-sensitive in queries. For the time being I've made an expression
> index on lower(KEY). But I would like to have just lower case data and
> then drop this expression index.
>
> 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 am not sure if the following helps, but I'm including the EXPLAIN on
> this table. Penny for your thoughts!

Whatever the issue, you can bet your car that it's not a bug in
PostgreSQL, but you who is misunderstanding how PG works.

Write a script that loops thru the records one by one, updating only
one record per loop iteration.  That will find the problem record.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGxRSUS9HxQb37XmcRApMyAKCGOmpm5xKkfuWR19OnbXLVZMMbkwCcCHmu
4OOXMnRnaixpp8lSjbrA/5w=
=M3jw
-----END PGP SIGNATURE-----