Thread: Case sensitivity
I am converting data from Access into Postgres and ran into an issue with case sensitivity. Can I write queries in Access that will be case insensitive without rewriting the queries. So I would like to know if this be handled in Postgres or even if someone knows in Access. Thank you.
Jason Tesser wrote: > I am converting data from Access into Postgres and ran into an issue > with case sensitivity. Can I write queries in Access that will be case > insensitive without rewriting the queries. So I would like to know if > this be handled in Postgres or even if someone knows in Access. Thank > you. I believe no. You can index on LOWER(f) or UPPER(f) but that still requires a query rewriting to WHERE LOWER(f) = .... You can also write INSERT triggers to convert values to upper case, but that still requires query rewriting if you're comparing with mixed-case values. This has been discussed many times. See the archive for the various threads on this. To be safe, if you want your application to be portable, you should always assume case-sensitivity. AFAIK, only Access, SQL Server, and MySQL that are case-insensitive by default while others including Postgres are case-sensitive. Any other popular DB that are case-sensitive out there? -- dave
On Tue, 10 Feb 2004 13:02:55 +0700, David Garamond <lists@zara.6.isreserved.com> wrote: > To be safe, if you want your application to be portable, you should > always assume case-sensitivity. AFAIK, only Access, SQL Server, and > MySQL that are case-insensitive by default while others including > Postgres are case-sensitive. Any other popular DB that are > case-sensitive out there? Sybase SQL Anywhere is not case sensitive by default. It has a setting to change it. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
On Tue, Feb 10, 2004 at 01:02:55PM +0700, David Garamond wrote:
>
> I believe no. You can index on LOWER(f) or UPPER(f) but that still
> requires a query rewriting to WHERE LOWER(f) = .... You can also write
> INSERT triggers to convert values to upper case, but that still requires
> query rewriting if you're comparing with mixed-case values.
But you could do some tricks with rules (plus the indexes and
triggers you mentioned) in order to lower() or upper() the input, if
you happen to know where you're likely to have the problems.
It's certainly true, however, that from the point of view of keeping
your data clean, you want these steps handled before the data
actually gets into the database.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner