Case sensitivity

[Rudy2315]

A familiar topic, I'm sure.

We are researching moving from SQL Server to Postgres. We are currently discussing the topic of case sensitivity and some possible solutions to the situation. We have looked into custom column collations, ILIKE, CITEXT so far as options.
We would like to explore the option of customizing the JDBC driver to handle both the DDL portion and data components of the query. in other words, we would turn
this: select Foo from Bar where Foo = 'TeXt'
into: select foo from bar where foo = 'text'

Obviously we have much more complex SQL (Application is 20+ years old) that would need to be processed but this gives a simple example. Is this something that could be done at the driver level?

[davecramer]

This automatically happens in postgres. Unless you quote "Foo" it will automatically seen as foo by the backend

[dhanathir]

@davecramer Thanks for your reply. That works as mentioned for column identifiers but to apply case insensitivity for String values comparison in the queries, is there an option to use at Database level? We have figured some ways to do it at column level in a table (custom collation or CITEXT extension) or at query level (using special operator like ILIKE) but we would like to know if there is a case insensitive collation setting that we could use to initialize a database or a Postgres JDBC driver setting.

[davecramer]

There is no global case insensitivity setting for String values in queries I'm afraid

[jorsol]

There is no global case insensitivity setting right now, not in the Driver nor in the Server, but as you already mentioned in your research ILIKE, citext type, lower(), and custom collation would do the trick, the thing is that this is cumbersome as it requires extra care to change columns, queries, etc... but anyway, this is part of the effort from moving of one db to another.

Just as an extra experiment, since PostgreSQL 15 (to be released on October 13, 2022), it allows ICU collations to be set as the default for clusters and databases, the only problem I see, is that it looks like it's not possible to set if a collation is either deterministic or nondeterministic so this might or might not help with your use case, but you need to give PostgreSQL 15 a try to check if it works for you.

[dhanathir]

@jorsol Thanks for your suggestion. I'm using Beta version of PostgreSQL 15 on Windows to check this and I haven't had success with creating a database with a custom collation, I'm using LOCALE 'en-US-u-ks-level2' for case insensitive US English and passing it as LC_COLLATE parameter for creating database.

[davecramer]

I'm curious what database allows you to do case insensitive predicates ?

[vlsi]

AFAIK, Oracle Database has NLS_SORT parameter that can be set at the global level, session level, and at index level: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/linguistic-sorting-and-matching.html#GUID-7FF18E5A-4ED6-4BDC-8059-886E9BD85E8F

[davecramer]

hmmm... I think it would be a good idea for the user to abandon this and change their code to be more compliant with PostgreSQL

[vlsi]

I guess PostgtreSQL allows creating custom collations, including "case insensitive" ones, so it might work in PostgreSQL as well: https://www.postgresql.org/docs/current/collation.html

Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), in order for such collations to act in a truly case- or accent-insensitive manner, they also need to be declared as not deterministic in CREATE COLLATION; see Section 24.2.2.4. Otherwise, any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

[davecramer]

Yes, it can be done.

[dhanathir]

Yes, PostgreSQL allows custom collation but only to be declared at a table column level. As mentioned in the earlier post by jorsol it seems to have been addressed in PostgreSQL 15 to have global setting using a non-deterministic custom collation for a database, but I haven't had luck making it work with beta version.

[davecramer]

Either way this is not a JDBC issue.