PostgreSQL QUOTE_NULLABLE Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL QUOTE_NULLABLE function to quote a string to be used as a string literal in SQL statements.

PostgreSQL QUOTE_NULLABLE Function Overview #

When using a string literal, you need to place its contents within single quotes. For example:

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

If the string literal contains a single quote, you must escape it by doubling the single quote. For example:

'Let''s Learn PostgreSQL.'Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we double the single quote (') in the word "Let's" to make the literal string valid for use in an SQL statement like this:

SELECT
  *
FROM
  books
WHERE
  title = 'Let''s Learn PostgreSQL';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the input string comes from an external source like an API call and you use it to construct a dynamic SQL, you have to sanitize the string literal using the QUOTE_NULLABLE() function.

The QUOTE_NULLABLE() function quotes a string literal using single quotes automatically. Additionally, it doubles a single quote inside the input string:

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

The QUOTE_NULLABLE function has one parameter:

• input_string: the input string you want to quote.

The function returns a string that is properly quoted.

If the input string is NULL, the function returns the string 'NULL'.

Note: The QUOTE_LITERAL function will return NULL if the input string is NULL. This is the main difference between QUOTE_NULLABLE and QUOTE_LITERAL functions.

PostgreSQL QUOTE_NULLABLE Function Examples #

The following example uses the QUOTE_NULLABLE function:

SELECT
  QUOTE_NULLABLE(E'Let\'s Learn PostgreSQL') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

          result
---------------------------
 'Let''s Learn PostgreSQL'Code language: plaintext (plaintext)

In this example:

• E'...' is an E-prefixed string. It is a special type of string literal that enables escape sequences. We use it to instruct PostgreSQL to treat backslashes (\) as escape characters.
• The QUOTE_NULLABLE function doubles the single quote (') in the input string.

The following query uses the QUOTE_NULLABLE function with a NULL input string:

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

Try it

Output:

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

It returns a 'NULL' string, not NULL. You can verify it using the following comparison:

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

Try it

Output:

 result
--------
 tCode language: plaintext (plaintext)

Using QUOTE_NULLABLE function to Generate Dynamic SQL #

First, define a user-defined function that gets filtered rows from a table dynamically:

CREATE OR REPLACE FUNCTION get_filtered_records(
   table_name TEXT,
   column_name TEXT,
   filter_value TEXT
)
RETURNS TABLE(result VARCHAR)
AS
$$
DECLARE
    sql_query TEXT;
BEGIN
    -- Construct an SQL query with proper NULL handling
    sql_query := 'SELECT ' || QUOTE_IDENT(column_name) ||
                 ' FROM ' || QUOTE_IDENT(table_name) ||
                 ' WHERE ' || QUOTE_IDENT(column_name) ||
                 CASE
                    WHEN filter_value IS NULL THEN ' IS NULL'
                    ELSE ' = ' || QUOTE_NULLABLE(filter_value)
                 END;

    -- Execute and return the result
    RETURN QUERY EXECUTE sql_query;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, call the get_filtered_records function to check if a work phone exists in the profiles table:

SELECT
  *
FROM
  get_filtered_records ('profiles', 'work_phone', '408-456-7890') 
  AS (work_phone VARCHAR);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  work_phone
--------------
 408-456-7890Code language: plaintext (plaintext)

Summary #

• Use the QUOTE_NULLABLE function to quote a string for use as a string literal in SQL statements.