Case-Insensitive Session Queries

[Deleted User]

Jim Knicely authored this tip.

By default, Vertica is case sensitive when it comes to comparing strings. You can change this behavior by setting the session locale to LEN_S1.

Example:

dbadmin=> CREATE TABLE test (c1 VARCHAR(25));
CREATE TABLE

dbadmin=> INSERT INTO test SELECT 'Vertica Rocks!';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM test WHERE c1 = 'VERTICA ROCKS!';
 c1
----
(0 rows)

dbadmin=> SHOW locale;
  name  |               setting
--------+--------------------------------------
 locale | en_US@collation=binary (LEN_KBINARY)
(1 row)

dbadmin=> SET LOCALE TO LEN_S1;
INFO 2567:  Canonical locale: 'en'
Standard collation: 'LEN_S1'
English
SET
dbadmin=> SELECT * FROM test WHERE c1 = 'VERTICA ROCKS!';
       c1
----------------
 Vertica Rocks!
(1 row)

Note that you will see a pretty hefty performance hit when using the LEN_S1 locale. A better option would be to store your data in all upper case or all lower case…

dbadmin=> ALTER TABLE test ADD COLUMN c2 VARCHAR(25) DEFAULT UPPER(c1);
ALTER TABLE

dbadmin=> ALTER TABLE test ADD COLUMN c3 VARCHAR(25) DEFAULT LOWER(c1);
ALTER TABLE

dbadmin=> SELECT * FROM test WHERE c2 = 'VERTICA ROCKS!';
       c1       |       c2       |       c3
----------------+----------------+----------------
 Vertica Rocks! | VERTICA ROCKS! | vertica rocks!
(1 row)

dbadmin=> SELECT * FROM test WHERE c3 = 'vertica rocks!';
       c1       |       c2       |       c3
----------------+----------------+----------------
 Vertica Rocks! | VERTICA ROCKS! | vertica rocks!
(1 row)

Have Fun!

Jim_Knicely

Make sure to heed the warning in the tip!

Note that you will see a pretty hefty performance hit when using the LEN_S1 locale

I get asked a lot if Vertica can be made to be case insensitive like SQL Server and MySQL. The answer to that is yeah, but is it a good idea? No... Mainly cause it'll wreak havoc on the query plans and they'll certainly be unoptimized. But I wanted to show you that Vertica can support case insensitivity