How can I turnoff case sensitive matching from Vertica ? Either Globally or session wise

Question

Can I turn case-sensitive data handling from Vertica off session wise . I want it to be dependent on user who may either want to keep it case-sensitive or otherwise !

Also Is there any key to be modified while logging in to mark the session on for Unicode data handling ?

Answer 1

There are indeed ways. I did not test them fully, so there might be corner cases I am not aware of. The keyword you are looking for is collation. You specifically want to update the colstrength keyword and you want a value of 1 I believe (case and accents are ignored).

You can do it in a few ways:

• vsql-only : \locale en_US@colstrength=1
• from anywhere including via ODBC/JDBC statements: SET LOCALE TO 'en_US@colstrength=1';
• by overriding the Locale value in your DSN (not tested) usually in /etc/odbc.ini for odbc

To show the effect, here is an example, first with the default, then after changing the locale:

\locale
en_US@collation=binary

select 'me' = 'ME';
 ?column?
----------
 f
(1 row)

SET LOCALE TO 'en_US@colstrength=1';

\locale
en_US@colstrength=1

select 'me' = 'ME';
 ?column?
----------
 t
(1 row)

I am pretty sure there is more to it, but this should get you started.