The quote_literal() function is a string function used in PostgreSQL. This function works somewhat similar to the quote_ident() function. The quote_literal() function takes in a string and returns the same string enclosed in single quotes. In this post, we’ll see what the quote_literal() function does and how is it used in Postgres.

What Does the PostgreSQL quote_literal() Function Do?

The PostgreSQL quote_literal() function gets a string as a parameter/argument and returns that string enclosed in single quotes. The syntax for the quote_literal() function can be written as follows:

quote_literal(str);

● The quote_literal() function takes the string and returns the string enclosed in single quotes.

● A NULL is returned if the argument given to the quote_literal() function is NULL. However, quote_nullable() is a better option in this case.

We will now understand the functioning and use cases of the quote_literal() function with the help of examples.

Example1: Understanding quote_literal() Function

We will execute the following query having the quote_literal() function to see how this function works.

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

By executing this query we will get the same string enclosed in single quotes like this:

Note that the quote_literal() function has returned NULL when we pass NULL as an argument.

This is how the quote_literal() function works.

Example 2: Using quote_literal() Function with Embedded Single Quotes

Let’s see how the query functions if there are embedded single quotes in the string. Consider the following query:

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

The quote_literal() will behave in the same manner as it usually does. The output for this query is:

The whole string is enclosed in single quotes.

Example 3: Using quote_literal() Function With Non-String Data types

The quote_literal() function can also take non-string data type as argument. For example, we can pass a boolean to see does it works with other data types or not. Consider the following query for that:

SELECT quote_literal(false) ;

By executing this query, we will get the following output:

This means that the quote_literal() function works fine with other non-string data types. Let’s observe another query, to see if it works in a similar way, with the integer, or not.

SELECT quote_literal(87394);

This query returns the following output.

We can see that the quote_literal() function also works fine with the non-string data types, unlike the quote_ident() function.

Let’s implement the quote_literal() function on the table data.

Example 3: Using quote_literal() Function on Table’s Data

Let’s apply the quote_literal() function on the table column “studentname” from the “registration” table.

The table looks like this:

We will write the following query to execute the quote_literal() function on the “studentname” column :

SELECT studentid, QUOTE_LITERAL(studentname), city
 FROM registration;

The above query will enclose every entry of the “studentname” with single quotes like this:

This is how we can use the quote_literal() function in PostgreSQL.

Conclusion

The PostgreSQL quote_literal() function takes in a string and returns the same string enclosed with the single quotations. This function can also take other non-string arguments and still work fine. This post taught us the workings of the quote_literal() function and its use cases in detail.