In PostgreSQL, we can use the quote_literal() function to quote strings within a SQL statement string.

Any embedded single-quotes and backslashes are properly doubled.

Examples

Here’s an example to demonstrate:

SELECT quote_literal('Homer');

Result:

'Homer'

Just to be sure, here’s a comparison of the same value quoted verses unquoted:

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

Result:

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

Here are a few more examples:

\x
SELECT 
    quote_literal('Homer Simpson') AS "'Homer Simpson'",
    quote_literal(E'Homer\'s IQ') AS "E'Homer\'s IQ'",
    quote_literal(' ') AS "' '",
    quote_literal('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_literal(100) AS "100",
    quote_literal(1.5) AS "1.5",
    quote_literal('2025-06-07'::date) AS "'2025-06-07'::date",
    quote_literal(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 null being returned:

SELECT quote_literal(null);

Result:

null

There’s also a quote_nullable() function that can be used instead, if you anticipate the argument to be null. The quote_nullable() function returns the actual string NULL if the argument is null.