Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #227

Re: Case-insensitive select

Show all headers | View raw

On Thu, 01 Sep 2011 16:40:58 +0200, Laurenz Albe wrote:

> Oh, I see. I think you got confused here, because the sentence you quote
> is a description of a drawback of the "standard approach" using "WHERE
> lower(col) = LOWER(?)", *not* a drawback of citext.

Seems you're right, I was mistaken.

mgogala=# create temporary table t1(col1 citext, constraint t1_pk primary 
key(col1));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pk" 
for table "t1"
CREATE TABLE
mgogala=# insert into t1 values('Laurenz Albe');
INSERT 0 1
mgogala=# insert into t1 values('laurenz albe');
ERROR:  duplicate key value violates unique constraint "t1_pk"
DETAIL:  Key (col1)=(laurenz albe) already exists.
mgogala=# insert into t1 values('LAURENZ ALBE');
ERROR:  duplicate key value violates unique constraint "t1_pk"
DETAIL:  Key (col1)=(LAURENZ ALBE) already exists.
mgogala=# 

However, the citext doesn't allow me to limit the column size:

mgogala=# create temporary table t1(col1 citext(10), constraint t1_pk 
primary key(col1));
ERROR:  type modifier is not allowed for type "citext"
LINE 1: create temporary table t1(col1 citext(10), constraint t1_pk ...

It seems to me that citext is a good replacement for the type text, not 
for the type varchar(n). At any rate, the type citext is useful but 
rather nonstandard. I would still use  the standard varchar type for the 
vast majority of cases and use trigger and/or functional indexes to 
resolve the case insensitive search. Case insensitive search was the 
problem for which functional indexes were invented. Cost based optimizers 
were having trouble with the functional indexes, Oracle's until version 
11 and the extended statistics, because the normal statistics gathering 
procedures usually do not collect data on lower(col1) or something 
similar to it. The trick to make it perform better was to add a column, 
named something like lower_col1 and populate it by a trigger. That would 
help the optimizer, at the expense of violating the relational rules and 
storing the duplicate information. That column could then be indexed 
normally. This method still provides the best performance, on all 
databases I know of, with the notable exception of Oracle 11i.

-- 
http://mgogala.byethost5.com

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Case-insensitive select RVince <rvince99@gmail.com> - 2011-08-31 05:29 -0700
  Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-31 13:19 +0000
  Re: Case-insensitive select Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-08-31 20:42 +0100
    Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-31 23:00 +0000
      Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-01 08:56 +0200
        Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-01 12:57 +0000
          Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-01 16:40 +0200
            Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-01 21:26 +0000
              Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-02 10:08 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-02 13:03 +0000
                Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-05 09:45 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-05 10:53 +0000
                Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-05 16:13 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-06 02:21 +0000
              Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-02 10:08 +0200

csiph-web