'How to handle quoted values in AWS Redshift unload command?

Suppose, following the AWS docs I'd like to use an unload command like 

unload
(
'SELECT * 
FROM table_name 
WHERE day = '2019-01-01' 
')
to 's3://bucket_name/path' 
iam_role 'arn:aws:iam::<aws acct num>:role/<redshift role>'
ADDQUOTES
ESCAPE
DELIMITER ','
GZIP
ALLOWOVERWRITE;

The problem is that the full query should be quoted and to write a string literal into the query will escape the string before the full query (as valid sql) is finished. How to escape quotes inside an AWS redshift unload statement?

Full disclosure, I know one way to do this but haven't seen a good link or SO question so figured I'd post one for others benefit.

sqlamazon-redshift


Solution 1:[1]

You can put the query between $$ symbols so that you don't have to worry about any kind of quotes

unload
(
$$SELECT * 
FROM table_name 
WHERE day = '2019-01-01' 
$$)
to 's3://bucket_name/path' 
iam_role 'arn:aws:iam::<aws acct num>:role/<redshift role>'
ADDQUOTES
ESCAPE
DELIMITER ','
GZIP
ALLOWOVERWRITE;

Solution 2:[2]

From UNLOAD - Amazon Redshift:

If your query contains quotes (for example to enclose literal values), put the literal between two sets of single quotation marks—you must also enclose the query between single quotation marks:

('select * from venue where venuestate=''NV''')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Community

Related Questions