Обсуждение: Possible to set postgres in case insensitive mode ?
Is it possible to set postgres in case insensitive mode ?
If so, how?
Thanks / Moe
If so, how?
Thanks / Moe
On Fri, 2010-01-29 at 23:52 +0200, Moe wrote: > Is it possible to set postgres in case insensitive mode ? I assume you mean the issue where postgres folds all case of objects to lower if they are not "". No. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
In response to Moe <mohamed5432154321@gmail.com>: > Is it possible to set postgres in case insensitive mode ? > > If so, how? Keywords are always case-insensitive. Identifiers are case-insensitive unless you surround them with "". Strings are case-sensitive unless you use ILIKE or similar methods of comparison. All of these conform to what I'm used to in other database systems. If the above doesn't answer your question, perhaps provide a little more detail about what you're trying to do and where you're having problems. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Fri, Jan 29, 2010 at 2:52 PM, Moe <mohamed5432154321@gmail.com> wrote: > Is it possible to set postgres in case insensitive mode ? > > If so, how? What part, exactly, do you want to be case insensitive? I assume you mean a text / varchar type? Look for citext, I believe it's a contrib module, until 9.0 is out, which will include it natively.
On Fri, Jan 29, 2010 at 7:40 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Fri, Jan 29, 2010 at 2:52 PM, Moe <mohamed5432154321@gmail.com> wrote: >> Is it possible to set postgres in case insensitive mode ? >> >> If so, how? > > What part, exactly, do you want to be case insensitive? I assume you > mean a text / varchar type? Look for citext, I believe it's a contrib > module, until 9.0 is out, which will include it natively. It's here: http://pgfoundry.org/projects/citext/ But it doesn't work in 8.3 or 8.4, only 8.2 and before. So either run that or wait for 9.0 I guess.
On Fri, 2010-01-29 at 23:52 +0200, Moe wrote: > Is it possible to set postgres in case insensitive mode ? I assume you mean the issue where postgres folds all case of objects to lower if they are not "". No. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
On Sat, Jan 30, 2010 at 4:44 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
It's here:On Fri, Jan 29, 2010 at 7:40 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Jan 29, 2010 at 2:52 PM, Moe <mohamed5432154321@gmail.com> wrote:
>> Is it possible to set postgres in case insensitive mode ?
>>
>> If so, how?
>
> What part, exactly, do you want to be case insensitive? I assume you
> mean a text / varchar type? Look for citext, I believe it's a contrib
> module, until 9.0 is out, which will include it natively.
http://pgfoundry.org/projects/citext/
But it doesn't work in 8.3 or 8.4, only 8.2 and before. So either run
that or wait for 9.0 I guess.
Sorry, I forgot I posted the message. What I mean was that a query select * from where email = ? could match on both upper and lower case emails, such myEmail@hotmail.com or myemail@hotmail.com
I know I can use the lower(...) function but this is not an option when using hibernate.
MySql is by default case insensitive, I just figured there'd be an option to turn it on in PG as well.
What about 9.0 ? How is that going to be offered ?
For now I just normalized all emails to lower cased, and changed so that they are always saved in lower.. but there are other columns that are likely to need similar mathing ( firstname, lastname, address ... ) where lowering is not an option, and where storing in a separate column is really ugly. The like possibility is probably just slow.
Sincerely / Moe
On Mon, Feb 01, 2010 at 07:35:45PM +0200, Moe wrote: > On Sat, Jan 30, 2010 at 4:44 AM, Scott Marlowe <scott.marlowe@gmail.com>wrote: > > > On Fri, Jan 29, 2010 at 7:40 PM, Scott Marlowe <scott.marlowe@gmail.com> > > wrote: > > > On Fri, Jan 29, 2010 at 2:52 PM, Moe <mohamed5432154321@gmail.com> > > wrote: > > >> Is it possible to set postgres in case insensitive mode ? > > >> > > >> If so, how? > > > > > > What part, exactly, do you want to be case insensitive? I assume you > > > mean a text / varchar type? Look for citext, I believe it's a contrib > > > module, until 9.0 is out, which will include it natively. > > > > It's here: > > http://pgfoundry.org/projects/citext/ > > But it doesn't work in 8.3 or 8.4, only 8.2 and before. So either run > > that or wait for 9.0 I guess. > > > > Sorry, I forgot I posted the message. What I mean was that a query select * > from where email = ? could match on both upper and lower case emails, such > myEmail@hotmail.com or myemail@hotmail.com > > I know I can use the lower(...) function but this is not an option when > using hibernate. > > MySql is by default case insensitive, I just figured there'd be an option to > turn it on in PG as well. You can use citext, a supplied module. > What about 9.0 ? How is that going to be offered ? When it's ready! :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate