PostgreSQL offers many string functions including the parse_ident(), quote_ident, quote_literal(), etc. There is another convenient function called the quote_nullable() that is commonly used to handle NULL situations. The quote_nullable() function works the same as the quote_literal() function. The difference lies just in the case when NULL is provided as a string.

Let’s understand the concept of the quote_nullable() function with examples.

What Does the quote_nullable() Function Do in PostgreSQL?

The quote_nullable() function takes a string as an argument/parameter and returns the same string enclosed in the single quotations. The basic syntax of the quote_nullable() function is as follows:

quote_nullable(str)

● The return data type of the quote_nullable() function is a string.

● If the NULL input is provided to the quote_nullable() function, it will return the string NULL. This is the difference between the quote_nullable() function and the quote_literal() function(we will see this later with the help of an example).

Below are some of the examples and use cases of the quote_nullable() function which will help us understand the concept of this function.

Example 1: Understanding the quote_nullable() Function

Consider the following query to understand how the quote_nullable() function works.

SELECT  
  quote_nullable('Command Prompt'),
  quote_nullable(null);

● The first statement will return the same string enclosed in single quotes.

● The second statement where NULL is passed will return the string NULL.

The output of this query will be the following:

This is the basic functioning of the quote_nullable() function. We’ll move towards some advanced examples now.

Example 2: Using quote_nullable() Function With Embedded Single Quotes

The function can encounter the case where the string can have embedded quotes. The function behaves in the same way as it normally does. Consider the below given query:

SELECT quote_nullable(E'let\'s learn PostgreSQL') ;

The above query will return the embedded string enclosed in the single quotations like this:

Example 3: Using quote_nullable() Function With Non-String Data Types

The quote_nullable() function also works with non-string data types. It works normally with the non-string data types. Let’s input an integer in the quote_nullable() function using the following query:

SELECT quote_nullable(4735) ;

The query will return the integer enclosed in the single quotation like this:

Similarly, other data types such as boolean, etc. work fine with the quote_nullable() function as well.

Example 4: Using quote_nullable() Function With Table’s Data

We will now implement the quote_nullable() function on the table’s data. Consider the “test_scores” table. The table looks like this:

Let’s implement the quote_nullable() function on the “candidate_gender” column of the above table. The query for this can be written as:

SELECT candidate_name, QUOTE_NULLABLE(candidate_gender)
FROM test_scores;

The query will return the Postgres table containing all the entries of the column “candidate_gender” enclosed in single quotes. The output of the query is:

This is how the quote_nullable() function works.

Till now we have seen the working of the quote_nullable() function which seemed to be the same as that of the quote_literal() function. So what’s the difference between the two functions? Let’s learn about it in the next section.

PostgreSQL quote_nullable() Function VS quote_literal() Function

The basic working of the quote_nullable() function and the quote_literal() function are the same. The difference between both functions comes when we have to encounter the case of NULL as an input. By using the following query we will demonstrate the difference between both the quote_nullable() function and the quote_literal() function.

SELECT  
  quote_literal(Null),
  quote_nullable(Null);

The query returns the following output:

In the above output, we can see that when the NULL is provided to the quote_literal() function, it returns the NULL. But when NULL is provided to the quote_nullable() function it gives us the string NULL. this is the whole difference between both functions.

Verification

The fact stated above can be verified by using the IS NULL statement. The “IS NULL” keyword is used to find if a column contains a NULL or not. The IS NULL returns TRUE if the column entry is NULL and it returns FALSE if the column entry is not NULL. This function of the IS NULL statement can be used to verify the return value of the quote_literal() and the quote_nullable() functions.

SELECT 
  quote_literal(Null) IS NULL AS quote_literal,
  quote_nullable(Null) IS NULL AS quote_nullable;

This query returns the following output:

The quote_literal() function returns NULL so the IS NULL statement has given TRUE while in the case of the quote_nullable() function, the string NULL is returned which means that the output is not NULL so the IS NULL statement has returned FALSE. Which verifies the working of both functions.

Conclusion

The quote_nullable() function in PostgreSQL is used to enclose the string within single quotations. The string is provided to the function as an argument and it returns the single quote enclosed string. The quote_nullable() function basically works the same as the quote_literal() function but the difference comes when we encounter the NULL case. This article demonstrated the working of the quote_nullable() function its use cases and the difference between the Postgres quote_nullable() function and the quote_literal() function.