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)
