Skip site navigation (1) Skip section navigation (2)

Re: uppercase = lowercase

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: Donald Fraser <demolish(at)cwgsy(dot)net>
Cc: "[ADMIN]" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: uppercase = lowercase
Date: 2003-02-18 18:07:07
Message-ID: 1045591627.12931.898.camel@camel (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-advocacy
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(at)storelandia(dot)com>
> jaleo8(at)storelandia(dot)com>
> To: <  <mailto:pgsql-admin(at)postgresql(dot)org> pgsql-admin(at)postgresql(dot)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

In response to


pgsql-admin by date

Next:From: Laurette CisnerosDate: 2003-02-18 18:57:06
Subject: Re: autocommit
Previous:From: FelipenascDate: 2003-02-18 17:41:26
Subject: Re: data-time type

pgsql-advocacy by date

Next:From: eleinDate: 2003-02-18 23:59:52
Subject: Language Lists? User Group Lists?
Previous:From: Bruce MomjianDate: 2003-02-18 02:49:31
Subject: Re: [HACKERS] Changing the default configuration (was Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group