On the surface, PostgreSQL’s quote_literal() and quote_nullable() functions appear to do the same thing. But there is a difference.

It all comes down to how they deal with null arguments; quote_literal() returns null while quote_nullable() returns the string NULL.

Example

Here’s an example to demonstrate how these two functions differ:

\pset null '(actual null value)'
SELECT 
    quote_nullable(null),
    quote_literal(null);

Result:

 quote_nullable |    quote_literal    
----------------+---------------------
 NULL           | (actual null value)

Here, the first line specifies a string to use whenever a null value is returned in my psql terminal. In this case, I specified that null values are returned as (actual null value).

We can see that only the quote_literal() function returned an actual null value when passed a null value. We know this because it returned (actual null value), which is the text that’s returned whenever there’s a null value.

The quote_nullable() function on the other hand, returned the string NULL – which is not an actual null value, and therefore (actual null value) was not used for this column.

When dealing with values that might be null, it’s usually advisable to use quote_nullable() instead of quote_literal(). See the PostgreSQL documentation for a more detailed discussion on this.