Thread: 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 Doc-postgresql-es@listas.hispalinux.es https://listas.hispalinux.es/mailman/listinfo/doc-postgresql-es
On Fri, 2003-02-14 at 08:51, jose antonio leo wrote: > How can I make selects not sensitive uppercase and lowercase characters? > This is possible modifying something of psql configuration? I take it you mean that SELECT x FROM t WHERE x = 'abc'; should also return rows where x = 'ABC' and x = 'Abc' and so on. The answer is to make your condition say what it means: SELECT x FROM t WHERE lower(x) = 'abc'. If you do this a lot, you may well want to create an index on lower(x). -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "God be merciful unto us, and bless us; and cause his face to shine upon us." Psalms 67:1
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" <jaleo8@storelandia.com>
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
Doc-postgresql-es@listas.hispalinux.es
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 majordomo@postgresql.org)
Attachment
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 >
Robert Treat <xzilla@users.sourceforge.net> writes:
> 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?
In the long run, the correct SQL-spec-compliant solution is selectable
collation for each column, and then you could specify a case-insensitive
collation for the columns you want to act this way. But I don't really
foresee that happening in the near future :-(. In the meantime, a
specialized datatype seems like an okay hack.
I'd not favor putting it into the core distribution, because that would
amount to a commitment to support it indefinitely, which I don't want
to make. contrib would be iffy for the same reason --- contrib tends
to have a subtext of "this might be mainstream someday, if it gets
polished up". gborg, no problem.
But that's just my $0.02. Comments anyone?
regards, tom lane
For any of you out there that have tried this module already please forgive me but there is a bug in the SQL install script. While I have tried and tested the code under a public schema, when I packaged up the install script I thought it would be tidier if it was all put into the pg_catalog schema (due to the large amount of overloaded functions). A simple append of pg_catalog in most cases worked but I overlooked the correct format for the CREATE OPERATOR statement within schemas (it was actually a cut and paste from pgAdmin II which gives the incorrect format - poor excuse I know!). While the original SQL script runs without any errors, and appears to work, it may have some undesirable side effects. I therefore suggest you use the updated attached install script (citext.sql - the other files remain unchanged). Regards Donald Fraser. Ps. I don't mind adding and maintaining the code under gborg, I just need some guidance on how to do it... anyone done this before? Tip: If you want to un-install the module, having already run the citext.sql script file, simply execute the following SQL: DROP FUNCTION citextin(cstring) CASCADE; This will remove anything to do with the citext data type. ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Robert Treat" <xzilla@users.sourceforge.net> Cc: "Donald Fraser" <demolish@cwgsy.net>; "[ADMIN]" <pgsql-admin@postgresql.org> Sent: Wednesday, February 19, 2003 7:51 AM Subject: Re: [ADMIN] uppercase = lowercase > Robert Treat <xzilla@users.sourceforge.net> writes: > > 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? > > In the long run, the correct SQL-spec-compliant solution is selectable > collation for each column, and then you could specify a case-insensitive > collation for the columns you want to act this way. But I don't really > foresee that happening in the near future :-(. In the meantime, a > specialized datatype seems like an okay hack. > > I'd not favor putting it into the core distribution, because that would > amount to a commitment to support it indefinitely, which I don't want > to make. contrib would be iffy for the same reason --- contrib tends > to have a subtext of "this might be mainstream someday, if it gets > polished up". gborg, no problem. > > But that's just my $0.02. Comments anyone? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Attachment
On Wed, 2003-02-19 at 02:51, Tom Lane wrote: > Robert Treat <xzilla@users.sourceforge.net> writes: > > 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? > > In the long run, the correct SQL-spec-compliant solution is selectable > collation for each column, and then you could specify a case-insensitive > collation for the columns you want to act this way. But I don't really > foresee that happening in the near future :-(. In the meantime, a > specialized datatype seems like an okay hack. > > I'd not favor putting it into the core distribution, because that would > amount to a commitment to support it indefinitely, which I don't want > to make. contrib would be iffy for the same reason --- contrib tends > to have a subtext of "this might be mainstream someday, if it gets > polished up". gborg, no problem. > > But that's just my $0.02. Comments anyone? > I tend to agree with this. Perhaps what we need is some type of meta-howto on converting from mysql to postgresql, which would list specific places to find articles, custom data types, custom functions, or code examples that folks might want if they are converting from mysql. This should probably also be done for other databases as well. This would give another option to the current debate on supporting informix syntax on hackers right now, put the patch in a gborg project, and list it as a resource in an informix meta-howto. Obviously this could be done for oracle and others.. I know the information is out there, but it's scattered, and given the number of times we see some of these questions, needs to be easier to find. Robert Treat
Robert Treat wrote: > > I'd not favor putting it into the core distribution, because that would > > amount to a commitment to support it indefinitely, which I don't want > > to make. contrib would be iffy for the same reason --- contrib tends > > to have a subtext of "this might be mainstream someday, if it gets > > polished up". gborg, no problem. > > > > But that's just my $0.02. Comments anyone? > > > > I tend to agree with this. Perhaps what we need is some type of > meta-howto on converting from mysql to postgresql, which would list > specific places to find articles, custom data types, custom functions, > or code examples that folks might want if they are converting from > mysql. This should probably also be done for other databases as well. > This would give another option to the current debate on supporting > informix syntax on hackers right now, put the patch in a gborg project, > and list it as a resource in an informix meta-howto. Obviously this > could be done for oracle and others.. I know the information is out > there, but it's scattered, and given the number of times we see some of > these questions, needs to be easier to find. This is what techdocs.postgresql.org is for. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Josh Berkus wrote: > Guys, > >>This is what techdocs.postgresql.org is for. > > I've been thinking for some time that we need a Postgresql resouce site that's > more open to multiple contributors than the current hard-coded HTML of > Techdocs. I was planning on experimenting in porting PostNuke to > PostgreSQL, but haven't had time. > > Thoughts, people? Sure. When Techdocs gets moved to the new Virtual Machine in a month or two it'll be a 100% Wiki styled thing. However, it'll have more control available than the standard "free for all" Wiki's. i.e. A page creator can request for their pages to be editable only by other Techdocs members or only by certain selected members, etc. Can create groups of users as well and assign access permissions to them, etc. So, the whole site will be based on a multiple contributor framework. The only drawback is that the software doesn't play friendly with languages other than English, so it won't be multi-lingual at this stage. :-/ Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Rod Taylor wrote: > On Thu, 2003-03-06 at 12:49, Josh Berkus wrote: > >>Guys, >> >>>This is what techdocs.postgresql.org is for. >> >>I've been thinking for some time that we need a Postgresql resouce site that's >>more open to multiple contributors than the current hard-coded HTML of >>Techdocs. I was planning on experimenting in porting PostNuke to >>PostgreSQL, but haven't had time. > > Step 1: Put all of the websites into CVS Ewww Yuk! Websites in CVS? That's a horrible thought. Sure, it's better than the present "people with accounts on the server can upload" mostly-manual approach, but it's not as good as Zwiki will do. Zwiki allows for good web based editing, it lets people be added to groups and have group permissions set, etc. It's very nifty. Regards and best wishes, Justin Clift > Step 2: Make them accessible by anonymous CVS > Step 3: Advertise somewhere how to get and change sources, and submit a > patch. > > I can't count the number of times I've sent in emails when I would have > preferred to send a patch. The only website I can find source for > (without trying hard) is developers.postgresql.org. -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Rod Taylor wrote: <snip> > Great.. How about advocacy? the portal? etc. Maybe all we have is code, > config files, and style sheets. Hmmm, putting the Advocacy site into CVS might be a win, because it's definitely not the kind of thing that Zwiki is good for. It's more of a code (that would go in CVS) and live database data (web based interface). The code for the web based interface could be put in CVS too. > Still, whatever tools you pick, configure, or change make it public > (where copyright allows). Agreed. It's more of a "time thing" than anything else. > Simple things like idocs.php or the jobs.php code could be improved upon > (code always can be), yet if I have an idea that may only take an hour > to implement it's near to impossible to find the sources -- so I've not > bothered. > > I'd be willing to bet there are a large number of people willing > (wanting) to do 10 minutes work here and there on the websites that > don't because it takes longer than that to find the materials. Probably a pretty accurate way of looking at it. It would be really good to spread the development effort for the websites into the PostgreSQL Community properly. The techdocs site is becoming so unmaintained that it's saddening. :( Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On Thu, 2003-03-06 at 12:49, Josh Berkus wrote: > Guys, > > > This is what techdocs.postgresql.org is for. > > I've been thinking for some time that we need a Postgresql resouce site that's > more open to multiple contributors than the current hard-coded HTML of > Techdocs. I was planning on experimenting in porting PostNuke to > PostgreSQL, but haven't had time. Step 1: Put all of the websites into CVS Step 2: Make them accessible by anonymous CVS Step 3: Advertise somewhere how to get and change sources, and submit a patch. I can't count the number of times I've sent in emails when I would have preferred to send a patch. The only website I can find source for (without trying hard) is developers.postgresql.org. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Guys, > This is what techdocs.postgresql.org is for. I've been thinking for some time that we need a Postgresql resouce site that's more open to multiple contributors than the current hard-coded HTML of Techdocs. I was planning on experimenting in porting PostNuke to PostgreSQL, but haven't had time. Thoughts, people? -- Josh Berkus Aglio Database Solutions San Francisco
On Thu, 2003-03-06 at 13:11, Rod Taylor wrote: > On Thu, 2003-03-06 at 12:49, Josh Berkus wrote: > > Guys, > > > > > This is what techdocs.postgresql.org is for. > > > > I've been thinking for some time that we need a Postgresql resouce > site that's > > more open to multiple contributors than the current hard-coded HTML of > > > Techdocs. I was planning on experimenting in porting PostNuke to > > PostgreSQL, but haven't had time. I've looked at it before. The hard part is reconciling the fact that so much logic has been put into the application to make up for the lack of features in mysql. Personally I think the zope/zwiki combination (that Justin has been promoting for the guides section) is fine for this type of thing, we just need someone to get a better handle on the situation. I have half a list done of some things that need to be done with techdocs, but have found time to get it finalized to really do anything about it. I suspect that Justin has the same issue; that is, he's pulled in so many different directions that techdocs quickly falls low on his priority list. > > Step 1: Put all of the websites into CVS > Step 2: Make them accessible by anonymous CVS > Step 3: Advertise somewhere how to get and change sources, and submit a > patch. > > I can't count the number of times I've sent in emails when I would have > preferred to send a patch. The only website I can find source for > (without trying hard) is developers.postgresql.org. > Good lord if this isn't true. I'm not sure why the current maintainers have shied away from it, but they have. I'm actually putting the finishing touches on a proposal for the www group to get things moved into CVS, it's my current "hot topic" I'm trying to focus on (after weekly news, newsgroups, phppgadmin stuff (development/website/demo site), oh and my job and wife and kids... yeesh.) I think I can finish it up today or tomorrow, it's nothing monumental, more of a statement of goals... Robert Treat
On Thu, 2003-03-06 at 13:21, Justin Clift wrote: > Rod Taylor wrote: > > On Thu, 2003-03-06 at 12:49, Josh Berkus wrote: > > > >>Guys, > >> > >>>This is what techdocs.postgresql.org is for. > >> > >>I've been thinking for some time that we need a Postgresql resouce site that's > >>more open to multiple contributors than the current hard-coded HTML of > >>Techdocs. I was planning on experimenting in porting PostNuke to > >>PostgreSQL, but haven't had time. > > > > Step 1: Put all of the websites into CVS > > Ewww Yuk! > > Websites in CVS? That's a horrible thought. Sure, it's better than the > present "people with accounts on the server can upload" mostly-manual > approach, but it's not as good as Zwiki will do. > Zwiki allows for good web based editing, it lets people be added to > groups and have group permissions set, etc. It's very nifty. Great.. How about advocacy? the portal? etc. Maybe all we have is code, config files, and style sheets. Still, whatever tools you pick, configure, or change make it public (where copyright allows). Simple things like idocs.php or the jobs.php code could be improved upon (code always can be), yet if I have an idea that may only take an hour to implement it's near to impossible to find the sources -- so I've not bothered. I'd be willing to bet there are a large number of people willing (wanting) to do 10 minutes work here and there on the websites that don't because it takes longer than that to find the materials. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Justin Clift wrote: > Josh Berkus wrote: > >Guys, > > > >>This is what techdocs.postgresql.org is for. > > > >I've been thinking for some time that we need a Postgresql resouce site > >that's more open to multiple contributors than the current hard-coded HTML > >of Techdocs. I was planning on experimenting in porting PostNuke to > >PostgreSQL, but haven't had time. > > > >Thoughts, people? > > Sure. When Techdocs gets moved to the new Virtual Machine in a month or > two it'll be a 100% Wiki styled thing. However, it'll have more control > available than the standard "free for all" Wiki's. i.e. A page creator > can request for their pages to be editable only by other Techdocs > members or only by certain selected members, etc. Can create groups of > users as well and assign access permissions to them, etc. > > So, the whole site will be based on a multiple contributor framework. > The only drawback is that the software doesn't play friendly with > languages other than English, so it won't be multi-lingual at this > stage. What language is it written in? My understanding is that current versions of Perl (5.8, at least) can handle Unicode natively now (so strings composed of Unicode characters are handled properly -- it just does the Right Thing with them), so if it's written in Perl it should Just Work -- or come close to it. -- Kevin Brown kevin@sysexperts.com
AFAIK the new site runs on zwiki, which is a zope based wiki engine, which is written in python. Robert Treat On Mon, 2003-03-31 at 04:10, Kevin Brown wrote: > Justin Clift wrote: > > Josh Berkus wrote: > > >Guys, > > > > > >>This is what techdocs.postgresql.org is for. > > > > > >I've been thinking for some time that we need a Postgresql resouce site > > >that's more open to multiple contributors than the current hard-coded HTML > > >of Techdocs. I was planning on experimenting in porting PostNuke to > > >PostgreSQL, but haven't had time. > > > > > >Thoughts, people? > > > > Sure. When Techdocs gets moved to the new Virtual Machine in a month or > > two it'll be a 100% Wiki styled thing. However, it'll have more control > > available than the standard "free for all" Wiki's. i.e. A page creator > > can request for their pages to be editable only by other Techdocs > > members or only by certain selected members, etc. Can create groups of > > users as well and assign access permissions to them, etc. > > > > So, the whole site will be based on a multiple contributor framework. > > The only drawback is that the software doesn't play friendly with > > languages other than English, so it won't be multi-lingual at this > > stage. > > What language is it written in? > > My understanding is that current versions of Perl (5.8, at least) can > handle Unicode natively now (so strings composed of Unicode characters > are handled properly -- it just does the Right Thing with them), so if > it's written in Perl it should Just Work -- or come close to it. > > > -- > Kevin Brown kevin@sysexperts.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html