QUOTE_NULLABLE

Quote a string as a SQL literal, returning the unquoted keyword NULL when the input is NULL.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

QUOTE_NULLABLE(str)

Description

## Overview Wraps the input string in single quotes and escapes embedded single quotes, identical to QUOTE_LITERAL for non-NULL inputs. The key difference is that when the input is NULL, QUOTE_NULLABLE returns the unquoted three-character string 'NULL' instead of a SQL NULL. This is exactly what you want when building dynamic SQL where a missing value must appear as the literal keyword NULL in the resulting statement. FORMAT's %L specifier applies QUOTE_NULLABLE semantics, so FORMAT('VALUES (%L)', NULL) produces the string 'VALUES (NULL)' rather than a SQL NULL. ## Behavior - Never returns NULL: a NULL input produces the literal three-character string 'NULL'. - Non-NULL input is wrapped in single quotes, with embedded single quotes escaped by doubling. - Empty string input produces two single quotes, not 'NULL'; the empty string is not treated as NULL. - Does not escape other characters; SQL does not require it inside a standard string literal. - Operates on Unicode code points. ## Compatibility - Matches the common SQL QUOTE_NULLABLE semantics. - Aligned with FORMAT's %L specifier.

Parameters

Name	Type	Description
`str`		Specifies the string value to quote. If NULL, the function returns the unquoted SQL keyword 'NULL' as a string.

Examples

-- Non-NULL value is quoted as a SQL literal
SELECT QUOTE_NULLABLE('hello');  -- '''hello'''

-- NULL input produces the unquoted keyword NULL
SELECT QUOTE_NULLABLE(CAST(NULL AS VARCHAR));  -- 'NULL'

-- Embedded single quotes are escaped
SELECT QUOTE_NULLABLE('it''s');  -- '''it''''s'''

-- Empty string is quoted, not treated as NULL
SELECT QUOTE_NULLABLE('');  -- ''''''

-- Build a WHERE clause that handles NULL inputs without a special case
SELECT FORMAT('SELECT * FROM retail.sales.orders WHERE customer_ref = %s', QUOTE_NULLABLE(@ref))
AS dynamic_sql;

-- Compose an INSERT row where some columns may be NULL
SELECT FORMAT('INSERT INTO audit.events (name, note) VALUES (%s, %s)',
              QUOTE_NULLABLE(name), QUOTE_NULLABLE(note))
FROM ops.etl.pending_events;

Pitfalls

QUOTE_NULLABLE never returns NULL. Downstream expressions that check for NULL after this function will never see it. Use QUOTE_LITERAL if you need NULL propagation.
Empty string is quoted, not treated as NULL. QUOTE_NULLABLE('') returns a string containing two single quotes, not 'NULL'. COALESCE empty-to-NULL upstream if that distinction matters.
The word 'NULL' returned for a NULL input is unquoted and uppercase. Do not compare it to the quoted string '''NULL''' expecting a match.
Use QUOTE_NULLABLE for string values, QUOTE_IDENT for identifiers. Swapping the two is a silent injection bug.