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

Groups > comp.databases.postgresql > #228

Re: Case-insensitive select

Show all headers | View raw

Mladen Gogala wrote:
> It seems to me that citext is a good replacement for the type text, not
> for the type varchar(n).

Well, it's called "citext" and not "civarchar" for a reason.
But you can easily

CREATE DOMAIN civarchar20 AS citext
   CONSTRAINT civarchar20_len CHECK(length(VALUE) <= 20);

to get that.

>                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.

It's everybody's choice.

>           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.

Of course it also wastes space space, and triggers don't come for
free either.

I doubt that using an index based on an expression would perform worse
in PostgreSQL, but I don't have the time to run a performance test.

At any rate statistics for such indexes are collected (look for rows
in pg_stats that belong to the index), and these statistice are exactly
the same as would be collected for the redundant column, so why should
there be a difference?

Yours,
Laurenz Albe 

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