PostgreSQL quote_nullable() Function

The PostgreSQL quote_nullable() function uses single quotes to quote a given string for using as a string literal in SQL statement strings.

If the string contains single quotes, it will be converted to two single quotes. For example a'b -> 'a''b'.

quote_nullable() Syntax

This is the syntax of the PostgreSQL quote_nullable() function:




Required. It can be any type. Non-string values ​​are treated as strings.

Return value

The PostgreSQL quote_nullable() function returns an escaped SQL string literal enclosed in single quotes.

If the input is null, this function returns a string NULL. This is where it differs from the quote_literal() function. So, if the arguments might be null , the quote_nullable() function is a better choice.

quote_nullable() Examples

Basic usage

This example demonstrates the basic usage of the quote_nullable() function.

    quote_nullable('abc') AS "abc",
    quote_nullable('How are you?') AS "How are you?",
    quote_nullable(null) AS "null";

  abc  |  How are you?  | null
 'abc' | 'How are you?' | NULL

From the above results we can see that:

  1. All strings are enclosed in single quotes.
  2. The parameter is null, the return value is a string NULL.

Embedded single quotes

If the string contains single quotes, each single quote will be converted to two single quotes. for example:

SELECT quote_nullable(E'Don\'t do that.');
 'Don''t do that.'

Non-string arguments

You can use a non-string argument to the quote_nullable() function , like:

SELECT quote_nullable(123.45);