Introduction

When an UNLOAD command takes a SQL query as a string literal, you have two quoting layers to think about. The outer layer is the string passed to UNLOAD, and the inner layer is the SQL inside that string. To include a single quote inside the inner SQL, you usually escape it by doubling it again.

The Real Problem Is Nested Quoting

In systems such as Amazon Redshift, UNLOAD often looks like this:

UNLOAD ('select * from customers')
TO 's3://my-bucket/export/'
IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole';

The query is already inside a quoted string. If the query itself needs a string literal containing an apostrophe, you must escape it for SQL inside SQL.

Double Quotes Are Not the Fix for String Literals

A common mistake is trying to use double quotes for string values.

-- wrong for SQL string literals
where last_name = "O'Brien"

In SQL, string literals use single quotes. Double quotes are generally for identifiers, not string content.

Escape a Single Quote by Doubling It

Inside normal SQL, the string O'Brien is written as O''Brien.

Inside UNLOAD, that inner SQL is itself inside a quoted string, so you still need the doubled single quote in the inner query.

1UNLOAD ('
2    select *
3    from customers
4    where last_name = ''O''''Brien''
5')
6TO 's3://my-bucket/export/'
7IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole';

This looks noisy, but it follows a consistent rule:

• the SQL literal O'Brien becomes O''Brien
• because that SQL is itself inside a string literal, the quote characters in the query text must survive that outer layer too

In practice, every single quote that belongs inside the query text must be doubled appropriately.

A Simpler Way to Think About It

Build the inner query first as if it were a normal standalone SQL statement.

select *
from customers
where last_name = 'O''Brien'

Then wrap that whole query as the UNLOAD string argument, preserving the inner escaping.

This mental split helps because it stops you from trying to solve both quoting layers at once.

QUOTE and ESCAPE Options Are About Output Format

Another source of confusion is the QUOTE or ESCAPE options on the UNLOAD command. Those control how exported file values are written, not how the SQL query string itself is parsed.

For example, this affects CSV output formatting:

1UNLOAD ('select id, name from customers')
2TO 's3://my-bucket/export/'
3IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole'
4CSV
5QUOTE AS '"';

That does not change the rule for escaping apostrophes inside the query text. Query parsing and file formatting are separate concerns.

Parameterization Is Better When Available

If the SQL is being generated in application code, manually building nested SQL strings can become fragile. When the surrounding system allows parameterization or safe query composition, use it.

If parameterization is not available for UNLOAD, construct the query carefully in code and treat every embedded literal as a quoting boundary that must be escaped for the target engine.

Test With a Minimal Query First

When debugging quote issues, reduce the statement to the smallest failing example.

UNLOAD ('select ''O''''Brien'' as name')
TO 's3://my-bucket/export/'
IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole';

If that works, the quote logic is correct and the remaining issue is elsewhere in the full query.

Common Pitfalls

• Confusing SQL string-literal escaping with CSV output escaping.
• Using double quotes for string values instead of single quotes.
• Trying to reason about the full nested statement without first writing the inner query correctly.
• Manually concatenating large SQL strings in application code without a clear escape strategy.
• Forgetting that UNLOAD adds an extra quoting layer around the query text.

Summary

• 'UNLOAD often requires nested SQL string quoting.'
• In SQL, embedded apostrophes are escaped by doubling single quotes.
• Build the inner SQL correctly first, then wrap it for UNLOAD.
• Output options such as QUOTE affect exported data formatting, not query-literal parsing.
• When debugging, reduce the statement to a minimal query that proves the escaping is correct.