Vertica Quick Tip: Case Insensitive Session Queries

Posted March 27, 2018 by Soniya Shah, Information Developer

Three 3D arrows, different colors pointing in different directions
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!