QUOTE_NULLABLE
Returns the given string suitably quoted for use as a string literal in an SQL statement string; or if the argument is null, returns the unquoted string NULL. Embedded single-quotes and backslashes are properly doubled.
Behavior Type
Syntax
QUOTE_NULLABLE ( string-expression )
Arguments
string-expression
|
Argument that resolves to one or more strings to format as string literals. If string-expression resolves to null value, QUOTE_NULLABLE returns NULL. |
Examples
The following examples use the table lead_vocalists, where the first names (fname) for Cher and Sting are set to NULL and an empty string, respectively
=> SELECT * from lead_vocalists ORDER BY lname DESC;
fname | lname | band
--------+---------+-------------------------------------------------
Stevie | Winwood | ["Spencer Davis Group","Traffic","Blind Faith"]
| Sting | ["Police"]
Grace | Slick | ["Jefferson Airplane","Jefferson Starship"]
Diana | Ross | ["Supremes"]
Mick | Jagger | ["Rolling Stones"]
| Cher | ["Sonny and Cher"]
(6 rows)
=> SELECT * FROM lead_vocalists WHERE fname IS NULL;
fname | lname | band
-------+-------+--------------------
| Cher | ["Sonny and Cher"]
(1 row)
=> SELECT * FROM lead_vocalists WHERE fname = '';
fname | lname | band
-------+-------+------------
| Sting | ["Police"]
(1 row)
The following query uses QUOTE_NULLABLE. Like QUOTE_LITERAL, QUOTE_NULLABLE sets off string values with single quotes, including empty strings. Unlike QUOTE_LITERAL, QUOTE_NULLABLE outputs NULL for null values:
=> SELECT QUOTE_NULLABLE (fname) "First Name", QUOTE_NULLABLE (lname) "Last Name", band FROM lead_vocalists ORDER BY fname DESC; First Name | Last Name | band ------------+-----------+------------------------------------------------- NULL | 'Cher' | ["Sonny and Cher"] 'Stevie' | 'Winwood' | ["Spencer Davis Group","Traffic","Blind Faith"] 'Mick' | 'Jagger' | ["Rolling Stones"] 'Grace' | 'Slick' | ["Jefferson Airplane","Jefferson Starship"] 'Diana' | 'Ross' | ["Supremes"] '' | 'Sting' | ["Police"] (6 rows)