|
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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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 _______________________________________________ Lista de correo de la traducción de PostgreSQL [EMAIL PROTECTED] https://listas.hispalinux.es/mailman/listinfo/doc-postgresql-es ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) |
citext.zip
Description: Zip compressed data
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
