Dollar-Quoted String Constants

[JimKnicely]

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:

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!';
                      ^

One solution would be to double every instance of the single quote character:

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)

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!

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)

Another example displays how we can use “dollar quoting” to easily insert data containing many single quotes into a table:

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)

Have fun!