From: | "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> |
---|---|
To: | "Postgres General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Strange inconsistency with UPDATE |
Date: | 2007-08-17 02:58:13 |
Message-ID: | e373d31e0708161958j507d4eadoc7c1b92c6c778b72@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
-PK.
-# EXPLAIN SELECT lower(ACOLUMN), count(*) FROM TABLE GROUP BY
lower(ACOLUMN) HAVING count(*) > 1 ;
QUERY PLAN
------------------------------------------------------------------------
GroupAggregate (cost=1031470.35..1171326.48 rows=4661871 width=10)
Filter: (count(*) > 1)
-> Sort (cost=1031470.35..1043125.03 rows=4661871 width=10)
Sort Key: lower((ACOLUMN)::text)
-> Seq Scan on TABLE (cost=0.00..228292.39 rows=4661871 width=10)
(5 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2007-08-17 03:23:00 | Re: Strange inconsistency with UPDATE |
Previous Message | Tyson Lloyd Thwaites | 2007-08-17 02:43:55 | Transaction auto-abort causes grief with Spring Framework |