While writing the PostgreSQL queries, we sometimes need to use the string as identifiers. For this purpose, the string needs to be converted into the quantified identifier or properly quoted in the double quotation mark. In PostgreSQL, the quote_ident() function performs this task. The quote_ident() function gets a string as an argument and returns the same string properly double-quoted that can be used as a qualified identifier.

Let’s learn more about the quote_ident() function in detail in the below section.

What Does PostgreSQL quote_ident() Function Do?

The PostgreSQL quote_ident() function takes in a string and returns the same string with appropriately double-quoted. The syntax for the quote_ident() function is given as:

quote_ident(str)

The function takes in the string and returns the string that is enclosed in double quotes. The quote_ident() function will return NULL if the specified string is NULL.

Let’s understand the concept with examples.

Example 1: Understanding quote_ident() Function

Consider the following query to understand the basic working of the quote_ident() function.

SELECT 
  quote_ident('Command   Prompt') AS "Command Prompt",
  quote_ident('Command-Prompt') AS "Command-Prompt",
  quote_ident('CommandPrompt') AS "CommandPrompt";

In the above query, we have provided the strings to the quote_ident() function. As a result, it will return the strings in the double quotes like this:

The quote_ident() function has returned the same strings but with the double quotes.

The double quotes are placed where they are required. If the string provided to the quote_ident() function is already an identifier, it is not enquoted in double quotes. Let’s understand this via an example.

Example 2: Using quote_ident() Function With Identifiers

The quote_ident() function does not always add double quotes on the string, usually, it does. But when the string provided to the quote_ident() function is already a qualified identifier, it will not encode it in double quotation marks. This can be illustrated by the following query:

SELECT 
  quote_ident('command_prompt') AS "command_prompt",
  quote_ident('command') AS "command",
  quote_ident('prompt') AS "prompt";

As we have provided qualified identifiers in the quote_ident() function, the function will not insert the double quotes on the string.

The above output verifies the fact that if we provide the qualified identifier to the function, it will not add the double quotes to it.

Example 3: Using quote_ident() Function on String Already Having Double Quotes

If there is a case that the string on which the qoute_ident() function is applied already has a double quote, the double quote will be applied to that double quote string. Here is a demonstration:

SELECT 
  quote_ident('Command "Prompt"') AS "doubling double quotes";

The output of this query will be the following:

This is how the qoute_ident() function doubles the double quote if it is already present in the string.

Example 4: Using quote_ident() Function On Table Data

Now we will observe the working of the qoute_ident() function on the table data. For that, consider the table named “registration”. The table looks like this:

Now we will apply the qoute_ident() function on the “city” column of this table. The query for this can be written as:

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

The query will encode all the entries of the “city” column in double quotes like this:

We can clearly see that the qoute_ident() function worked on the city table and returned the table with double-quoted entries of the column “city”.

This was all about the qoute_ident() function and its working.

Conclusion

The qoute_ident() function adds double quotes to the string to make it able to be used as a quantified identifier. The string is provided to the function as an argument and the qoute_ident() function returns the same string enquoted with double quotes. If the string provided to the function is already a quantified identifier, then no quotes will be added. In this Postgres blog, we have discussed the qoute_ident() function with various use cases.