PostgreSQL QUOTE_LITERAL Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL QUOTE_LITERAL function to escape string literals.

Introduction to PostgreSQL QUOTE_LITERAL Function #

The QUOTE_LITERAL function allows you to escape a string literal safely. It does so by doubling single quotes and backslashes inside the input string.

Here’s the syntax of the QUOTE_LITERAL function:

QUOTE_LITERAL(input_string)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• input_string is the string you want to escape.

The QUOTE_LITERAL function returns a new string enclosed in single quotes, with any internal backslashes and single quotes properly escaping.

The QUOTE_LITERAL function returns NULL if the input string is NULL.

If you want to get a NULL string instead, you can use the QUOTE_NULLABLE function.

The QUOTE_LITERAL function helps construct safe, dynamic SQL queries to prevent syntax errors and SQL injection attacks.

PostgreSQL QUOTE_LITERAL Function example #

The following example uses the QUOTE_LITERAL function to escape a string literal:

SELECT
  QUOTE_LITERAL(E'It\'s pgtutorial.com!') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this example:

• E'...' defines an escape string constant that allows PostgreSQL to interpret backslashes (\) as escape characters.
• Use a backslash (\) to properly escape the single quote (') in "It's".

Output:

         result
-------------------------
 'It''s pgtutorial.com!'Code language: plaintext (plaintext)

The output indicates that the QUOTE_LITERAL function escapes a single quote (') in "It's" by doubling it.

Handling NULL Values #

The following example returns NULL because the input string is NULL:

SELECT
  QUOTE_LITERAL(NULL) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 NULLCode language: plaintext (plaintext)

Summary #

• Use the QUOTE_LITERAL function to safely escape a string literal, preventing syntax errors in dynamic SQL queries.