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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
