Thread: Re: PostgreSQL and case insensitivity...
Guys, I'm forwarding this email for your comments. On January 15, 2006 09:51 am, Charles Phares wrote: > Hi Robert, > > Thanks for taking the time to respond. Yet another solution which I > was unaware of. Must do a closer read of the documentation! :) > > The regexp functions are great for doing equality tests, but they > will not help with ranged searches or inequality tests. (i.e. > greater than or less than) > > I really require full-feature support for case insensitivity. > > The project page for CITEXT -- http://gborg.postgresql.org/project/ > citext/faq/faq.php?faq_id=105 -- claims that PostgreSQL is unable to > define the collating sequence of characters. This seems to be the > root of my problem. I know that other database systems -- Sybase & > Microsoft SQL Server for instance -- use a user selectable server > wide collation sequence to implement case insensitivity. This seems > to be the best solution for portability and compatibility with other > RDBMS systems. > > What would it take to rally the PostgreSQL community to embrace this > problem?
The problem with using colation order is that it's database-wide. If you then need case-sensitive ordering on some field you're outa luck. If you could define colation on a per-field basis it would be a bit different. I'm also not sure what this has to do with advocacy... On Sun, Jan 15, 2006 at 10:35:28AM -0500, Robert Bernier wrote: > Guys, > > I'm forwarding this email for your comments. > > On January 15, 2006 09:51 am, Charles Phares wrote: > > Hi Robert, > > > > Thanks for taking the time to respond. Yet another solution which I > > was unaware of. Must do a closer read of the documentation! :) > > > > The regexp functions are great for doing equality tests, but they > > will not help with ranged searches or inequality tests. (i.e. > > greater than or less than) > > > > I really require full-feature support for case insensitivity. > > > > The project page for CITEXT -- http://gborg.postgresql.org/project/ > > citext/faq/faq.php?faq_id=105 -- claims that PostgreSQL is unable to > > define the collating sequence of characters. This seems to be the > > root of my problem. I know that other database systems -- Sybase & > > Microsoft SQL Server for instance -- use a user selectable server > > wide collation sequence to implement case insensitivity. This seems > > to be the best solution for portability and compatibility with other > > RDBMS systems. > > > > What would it take to rally the PostgreSQL community to embrace this > > problem? > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Just for the record, you can create a data type and implement the sort order you want on that data type. This is done by overriding the > < = operators and functions. A standard example for illustra/informix is/was name fields which sorted Mc Mac names together. To use regex stuff you would need to override those functions. --elein elein@varlena.com On Mon, Jan 16, 2006 at 02:30:25PM -0600, Jim C. Nasby wrote: > The problem with using colation order is that it's database-wide. If you > then need case-sensitive ordering on some field you're outa luck. If you > could define colation on a per-field basis it would be a bit different. > > I'm also not sure what this has to do with advocacy... > > On Sun, Jan 15, 2006 at 10:35:28AM -0500, Robert Bernier wrote: > > Guys, > > > > I'm forwarding this email for your comments. > > > > On January 15, 2006 09:51 am, Charles Phares wrote: > > > Hi Robert, > > > > > > Thanks for taking the time to respond. Yet another solution which I > > > was unaware of. Must do a closer read of the documentation! :) > > > > > > The regexp functions are great for doing equality tests, but they > > > will not help with ranged searches or inequality tests. (i.e. > > > greater than or less than) > > > > > > I really require full-feature support for case insensitivity. > > > > > > The project page for CITEXT -- http://gborg.postgresql.org/project/ > > > citext/faq/faq.php?faq_id=105 -- claims that PostgreSQL is unable to > > > define the collating sequence of characters. This seems to be the > > > root of my problem. I know that other database systems -- Sybase & > > > Microsoft SQL Server for instance -- use a user selectable server > > > wide collation sequence to implement case insensitivity. This seems > > > to be the best solution for portability and compatibility with other > > > RDBMS systems. > > > > > > What would it take to rally the PostgreSQL community to embrace this > > > problem? > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: explain analyze is your friend > > > > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: 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 >
On Monday 16 January 2006 15:30, Jim C. Nasby wrote: > The problem with using colation order is that it's database-wide. If you > then need case-sensitive ordering on some field you're outa luck. If you > could define colation on a per-field basis it would be a bit different. > > I'm also not sure what this has to do with advocacy... I posted here because of the nature of the question. Which mail-list do you think this is most appropriate?
Robert Bernier wrote: > On Monday 16 January 2006 15:30, Jim C. Nasby wrote: > > The problem with using colation order is that it's database-wide. If you > > then need case-sensitive ordering on some field you're outa luck. If you > > could define colation on a per-field basis it would be a bit different. > > > > I'm also not sure what this has to do with advocacy... > > > I posted here because of the nature of the question. Which mail-list do you think this is most appropriate? I have trouble understanding how this matches "advocacy" in your mind. Anyway, general would be best for this. -- 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
On Tue, Jan 17, 2006 at 04:02:26AM -0500, Robert Bernier wrote: > On Monday 16 January 2006 15:30, Jim C. Nasby wrote: > > The problem with using colation order is that it's database-wide. If you > > then need case-sensitive ordering on some field you're outa luck. If you > > could define colation on a per-field basis it would be a bit different. > > > > I'm also not sure what this has to do with advocacy... > > > I posted here because of the nature of the question. Which mail-list do you think this is most appropriate? -general; maybe -hackers if it was a specific call for action. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461