Vertica Quick Tip: Case Insensitive Session Queries

This blog post was authored by Jim Knicely. 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!