In PostgreSQL, the quote_nullable() function returns a given string suitably quoted to be used as a string literal in a SQL statement string. It returns the actual string NULL if the argument is null.

Any embedded single-quotes and backslashes are properly doubled.

Examples

Here’s an example to demonstrate:

SELECT quote_nullable('Homer');

Result:

'Homer'

Here’s a comparison of the same value when quoted vs unquoted:

SELECT 
    'Homer' AS "Unquoted",
    quote_nullable('Homer') AS "Quoted";

Result:

 Unquoted | Quoted  
----------+---------
 Homer    | 'Homer'

Here are a few more examples:

\x
SELECT 
    quote_nullable('Homer Simpson') AS "'Homer Simpson'",
    quote_nullable(E'Homer\'s IQ') AS "E'Homer\'s IQ'",
    quote_nullable(' ') AS "' '",
    quote_nullable('100') AS "'100'";

Result (using vertical output):

'Homer Simpson' | 'Homer Simpson'
E'Homer\'s IQ'  | 'Homer''s IQ'
' '             | ' '
'100'           | '100'

Passing a Non String

It’s possible to pass non string values, such as numbers and dates. In such cases, the result will be output as text:

SELECT 
    quote_nullable(100) AS "100",
    quote_nullable(1.5) AS "1.5",
    quote_nullable('2025-06-07'::date) AS "'2025-06-07'::date",
    quote_nullable(ARRAY[1,2,3]) AS "ARRAY[1,2,3]";

Result (using vertical output):

100                | '100'
1.5                | '1.5'
'2025-06-07'::date | '2025-06-07'
ARRAY[1,2,3]       | '{1,2,3}'

Passing a Null Value

Passing a null value results in an actual string of NULL being returned:

SELECT quote_nullable(null);

Result:

NULL

The above result is the actual string NULL, as opposed to a null value. We can check this by setting our output for null values, then running the statement again. To really demonstrate the result, we can run quote_nullable() alongside quote_literal() to compare the output when passed a null value:

\pset null '(actual null value)'
SELECT 
    quote_nullable(null),
    quote_literal(null);

Result:

 quote_nullable |    quote_literal    
----------------+---------------------
 NULL           | (actual null value)

Here, the first line configures psql so that any actual null values are returned as (actual null value). We can see that only the quote_literal() function returned an actual null value. The quote_nullable() function returned the string NULL, which is not an actual null value.