# PostgreSQL quote_nullable() Function

> 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 i…

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**](<https://www.commandprompt.com/education/postgresql-is-null-operatorcondition/>)[ statement](<https://www.commandprompt.com/education/postgresql-is-null-operatorcondition/>). 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 NUL** L 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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-quote_nullable-function/)