quote_nullable()

A function which formats arbitrary input as an SQL string literal and converts NULL to a string

quote_nullable() is a system function which formats arbitrary input as an appropriately quoted and escaped SQL string literal. If the input is NULL, the string 'NULL' is returned.

quote_nullable() was added in PostgreSQL 8.4.

Usage

quote_nullable ( text ) → text
quote_nullable ( anyelement ) → text

The string returned by quote_nullable() is suitable for use as a string literal in an SQL string. In contrast to quote_literal(), it will not return a NULL value (instead returning the string 'NULL'); this makes it suitable for use in those situatons where an actual NULL value would invalidate the entire string. See the Examples section for further details.

Change history

Examples

Basic usage example for quote_nullable():

postgres=# SELECT quote_nullable('foo \''bar');
 quote_nullable 
----------------
 E'foo \\''bar'
(1 row)

Non-string data types will be converted to text:

postgres=# SELECT quote_literal(42.195);
 quote_literal 
---------------
 '42.195'
(1 row)

If the provided value is NULL, the string 'NULL' is returned:

postgres=# SELECT quote_nullable(NULL), quote_nullable(NULL) IS NULL;
 quote_nullable | ?column? 
----------------+----------
 NULL           | f
(1 row)

If generating a dynamic query in PL/pgSQL, and any values being quoted might contain NULLquote_literal() will result in an error similar to the following:

postgres=# DO
           $$
           BEGIN
             EXECUTE
               ' UPDATE foo SET val = ' || quote_literal(NULL) ||
               '  WHERE id = ' || quote_literal(NULL);
           END;
           $$;
ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function inline_code_block line 3 at EXECUTE

as concatenation of a NULL value to a string type will result in a NULL value, e.g.:

postgres=# SELECT 'foo ' || NULL || ' bar' IS NULL;
 ?column? 
----------
 t
(1 row)

quote_nullable() prevents this issue:

postgres=# DO
           $$
           BEGIN
             EXECUTE
               ' UPDATE foo SET val = ' || quote_nullable(NULL) ||
               '  WHERE id = ' || quote_nullable(NULL);
           END;
           $$;
DO

Categories

String manipulation, System function

See also

quote_literal(), quote_ident()