PostgreSQL offers many string functions. Some of these functions offer the functionality to get some information related to the provided string while some of the functions are used to manipulate the string. The parse_ident() function is a string function that splits the qualified identifier into an array. In this article, we’ll learn about the parse_ident() function with the help of proper examples. Let’s learn together.

How Does PostgreSQL parse_ident() Function Work?

The parse_ident() function takes in an argument/qualified identifier and splits it into an array. The basic syntax of parse_ident() function can be written as:

parse_ident (qualified_arg, StrictMode)

In the above syntax:

● The parse_ident() function basically takes in two arguments/parameters.

● The 1st parameter is the qualified argument that will be split into the array.

● The 2nd parameter is an optional parameter that is used to enable or disable the strict mode. The strict mode is enabled by default i.e. if we do not specify this parameter in the function.

● If the strict mode is enabled, it will cause an error in case there is a special character present in the qualified identifier.

● If the strict mode is disabled i.e. by specifying it as FALSE, it will just ignore the special characters.

The function will return an array. In case, the user provides an invalid argument to the function, this will cause an error. The return value will be NULL if we provide the NULL values as an identifier/argument.

We will understand the parse_ident() function with the help of examples to make it more clear.

Example 1: Understanding the parse_ident() Function

We will consider the following query to observe the working of the parse_ident() function.

SELECT parse_ident('John.Smith.Lily');

In the above query, we have provided the “John.Smith.Lily” as a qualified identifier/argument. The function will simply return and split the argument in an array like this:

We can see that the argument has been split in an array.

Here in the above query, no information about the strict mode has been provided. This means that the strict mode is enabled because the default value for it is “enabled”. We will now see how the function responds to the special character when the strict mode is enabled.

Example 2: Understanding the Strict Mode in parse_ident() Function

Consider the following query to see the response of the function with the special characters. Let's add some special characters at the end of identifier arguments in the above-considered query.

SELECT parse_ident('John.Smith.Lily%%%');

Here we have inserted the % special character at the end of the identifier with the enabled strict mode. Upon execution, the query will throw an error like this:

The error says that we have provided an invalid string. In the next example, we will try to implement the parse_ident() function on the same qualified identifier/argument containing the special character at the end but with the disabled strict mode.

Example 3: Understanding the Disabled Strict Mode in parse_ident() Function

To disable the strict mode, we will specify it as FALSE. The query will become:

SELECT parse_ident('John.Smith.Lily%%%', FALSE);

The output of the query is:

We can observe that the output of the above query is totally different from the previous case. In that case, the query returned an error because the strict mode was enabled. But in the case of disabled strict mode, the function has ignored the special characters and executed the query without an error.

This is how the parse_ident() function splits the argument into an array.

Conclusion

The PostgreSQL parse_ident() function splits the qualified identifier/argument into an array. It gets two parameters; the 1st is the argument that is to be split, and the 2nd is the strict mode which is optional. But by default, it is enabled. Enabling the strict mode adds extra strictness towards the special characters in the argument. It will show an error if the strict mode is enabled and a special character is added to the identifier. While if it is disabled, the function will ignore the special character and return the result accordingly. This blog covered the concepts of the parse_ident() function with examples.