Re: uppercase = lowercase - Mailing list pgsql-admin

We get this question a lot, usually from folks converting from mysql.
Do any of the core folks think this is worth adding to contrib? Or
perhaps a project on gborg would be more appropriate?

Robert Treat

On Mon, 2003-02-17 at 16:54, Donald Fraser wrote:
> In my opinion there are no usefull solutions for this unless you always
> use single column indexing.
> For example:
> SELECT * FROM mytable WHERE lower(mycolumn) <= 'mycondition' ORDER BY
> lower(mycolumn);
> will give case insensitive ordering and if you want it to use an index
> for perfomance then you need to create a function index such as:
> CREATE INDEX myindex ON mytable USING btree (lower(mycolumn));
> That's fine for single column ordering but beyond that you have to
> create custom functions to combine multiple columns into one data type.
> For example you cannot create an index like:
> CREATE INDEX myindex ON mytable USING btree (lower(mycolumn),
> lower(mysecondcolumn));
> But you can create:
> CREATE INDEX myindex ON mytable USING btree (myfunction(mycolumn,
> mysecondcolumn));
> This is when I think it is not useful because quite often you need more
> than one column for indexing and you don't want to have to be creating
> an assortment of custom functions for every combination of column type
> you might choose.
>
> What I have done is to create a new data type called citext (case
> insensitive text) which stores and retrieves data exactly as text type
> does but when it comes to operators such as >,<,>=,<= and = it uses case
> insensitive comparisons.
> All my problems were solved, no more having to type lower() all over the
> place, making custom functions for multiple column indexing etc...
> I have attached the source code, which can be built in the normal
> "contrib" fashion for anyone interested.
> To save opening the zip file to find out whether it's of interest I have
> included the readme file contents.
> If anyone thinks it is worth adding to the "contrib" for the general
> public please do so.
>
> Here's the contents of the readme file:
> ===============================================================
> Module citext created by Donald Fraser.
> First release to public: 17th Feb. 2003.
> Files: citext.c, Makefile, citext.sql, readme.citext
> This module was created to implement a Case-Insensitive text data type
> named citext.
> citext uses the same storage (input and output) as text and is therefore
> totally
> compatible with any existing functions that use the text data type. For
> this reason
> I used function over-loading on most of the in-built functions that take
> text
> parameters.
> citext only supports btree for indexing.
> citext is not as efficient as text because the operator functions
> (<,<=,=,>=,>) and
> the btree compare function will make copies of the data and convert it
> to lower case
> for comparisons. There is room here for optimisations so that data isn't
> copied
> unnecessarily, but as a first attempt no major optimisations were
> considered.
> You can type cast citext into any of the data types that you can
> type-cast text into
> including of course text.
> The in-built functions that were not over-loaded to take citext are:
> split_part, position, quote_ident, quote_literal, replace, translate,
> substr,
> strpos, timezone. You can use these with a cast if you really want (Some
> of the
> substr functions were over-loaded but not all).
> Regular expressions:
> Because the data type is inherently case-insensitive many of the regular
> expressions
> produce the same result.
> That is ~ and ~* are the same as is:
> !~ and !~*
> ~~ and ~~*
> !~~ and !~~*
> I had to make a decision on casting between types for regular
> expressions and decided
> that if any parameter is of citext type then case insensitive applies.
> For example
> applying regular expressions with a varchar and a citext will produce a
> case-
> insensitive result.
> Having thought about this afterwards I realised that since we have the
> option to use
> case-insensitive results with regular expressions I should have left the
> behaviour
> exactly as text and then you have the best of both worlds... oh well not
> hard to
> change for any of you perfectionists!
> Installation:
> make install
> run the sql scrip in file citext.sql, for example:
> psql -d template1 -f citext.sql -o citext.out postgres
> =================================================================
>
> ----- Original Message -----
> From: "jose antonio leo" <  <mailto:jaleo8@storelandia.com>
> jaleo8@storelandia.com>
> To: <  <mailto:pgsql-admin@postgresql.org> pgsql-admin@postgresql.org>
> Sent: Friday, February 14, 2003 8:51 AM
> Subject: [ADMIN] uppercase = lowercase
>
>
>
> Hi!!
>
> How can I make selects not sensitive uppercase and lowercase characters?
> This is possible modifying something of psql configuration?
>
> Thanks
>