How to handle quoted values in AWS Redshift unload command?

Question

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.

Answer 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; 

Answer 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''')