Possible to set postgres in case insensitive mode ?
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 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.
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.comI 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