In PostgreSQL, the parse_ident() function splits a qualified identifier into an array of identifiers, and removes any quoting of individual identifiers.

We pass the qualified identifier to the function when we call it. We can also specify whether or not to use strict mode.

Example

Here’s an example to demonstrate:

SELECT parse_ident('postgres.pets.petname');

Result:

{postgres,pets,petname}

The various parts that make up the qualified name are separated and presented as a separate array element.

Depending on your SQL client, it may be displayed like this:

["postgres", "pets", "petname"]

Either way, it’s returned as an array with each part represented as an element in the array.

Dealing with Spaces

If there’s a space in any part of the qualified name, we must enclose that part in double quotes:

SELECT parse_ident('"my database".pets.petname');

Result:

{"my database",pets,petname}

Here’s what happens when I remove the double quotes:

SELECT parse_ident('my database.pets.petname');

Result:

ERROR:  string is not a valid identifier: "my database.pets.petname"

Removing Strict Mode

By default, parse_ident() operates in strict mode, which results in an error if there are any characters after the last identifier. But we have the option of disabling strict mode so that such an error doesn’t arise. We can do this by passing a second argument of false.

Take the following path for example:

SELECT parse_ident('postgres.pets.petname oops');

Result:

ERROR:  string is not a valid identifier: "postgres.pets.petname oops"

In this case I added a space and some text to the end of the identifier, which resulted in an error. The error occurred because strict mode is enabled by default.

We can also explicitly specify strict mode by including a second argument of true:

SELECT parse_ident('postgres.pets.petname oops', true);

Result:

ERROR:  string is not a valid identifier: "postgres.pets.petname oops"

But here’s what happens when I disable strict mode:

SELECT parse_ident('postgres.pets.petname oops', false);

Result:

{postgres,pets,petname}

This time the array was returned without error.

But we need to be mindful that disabling strict mode could also result in the results being screwed up:

SELECT parse_ident('my database.pets.petname', false);

Result:

{my}

In this case I forgot to put double quotes around my database, and instead of returning an error, the function returned everything up until the first space, which is not the result I wanted.