The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Case-Insensitive Session Queries
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
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 | [email protected]=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!
0
Comments
Make sure to heed the warning in the tip!
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