Dollar-Quoted String Constants
Posted: Thu Mar 07, 2013 3:04 pm
Hi,
It’s not an uncommon situation where you’ll be attempting to cope with data that contains many single quotes. It’s not a simple task to read and write data of this type because the single quote in Vertica is used to indicate the start and end of a string literal. Vertica can’t tell the difference between a single quote inside of the string and those that are supposed to enclose the string.
For example, if I try the following simple SELECT of a string value that contains single quotes, I’ll get an error:
One solution would be to double every instance of the single quote character:
But that’s a bit tedious. This’s why PostgreSQL provides another way, called “Dollar Quoting”. Since Vertica finds its roots in PostgreSQL we can utilize many of its features!
Another example displays how we can use “dollar quoting” to easily insert data containing many single quotes into a table:
Have fun!
It’s not an uncommon situation where you’ll be attempting to cope with data that contains many single quotes. It’s not a simple task to read and write data of this type because the single quote in Vertica is used to indicate the start and end of a string literal. Vertica can’t tell the difference between a single quote inside of the string and those that are supposed to enclose the string.
For example, if I try the following simple SELECT of a string value that contains single quotes, I’ll get an error:
Code: Select all
dbadmin=> SELECT 'Jim's dog ate Bill's cat's pet mouse's cheese!';
ERROR 4856: Syntax error at or near "dog" at character 15
LINE 1: SELECT 'Jim's dog ate Bill's cat's pet mouse's cheese!';
^
Code: Select all
dbadmin=> SELECT 'Jim''s dog ate Bill''s cat''s pet mouse''s cheese!' AS "Single Quote Doubled";
Single Quote Doubled
------------------------------------------------
Jim's dog ate Bill's cat's pet mouse's cheese!
(1 row)
Code: Select all
dbadmin=> SELECT $$Jim's dog ate Bill's cat's pet mouse's cheese!$$ AS "dollar-quoted";
dollar-quoted
------------------------------------------------
Jim's dog ate Bill's cat's pet mouse's cheese!
(1 row)
Code: Select all
dbadmin=> CREATE TABLE t (c VARCHAR(100));
CREATE TABLE
dbadmin=> INSERT INTO t VALUES ($$Jim's dog ate Bill's cat's pet mouse's cheese!$$);
OUTPUT
--------
1
(1 row)
dbadmin=> SELECT c FROM t;
c
------------------------------------------------
Jim's dog ate Bill's cat's pet mouse's cheese!
(1 row)