While working with the PostgreSQL database, identifying the appropriate schema name is crucial to access and manage the objects within that schema effectively. For this purpose, PostgreSQL offers a couple of methods, such as CURRENT_SCHEMA, and CURRENT_SCHEMAS. Moreover, users can obtain a list of all schemas using various commands and metacommands.

This post illustrates how to get the name of the current schema using the below-provided content:

- Method 1: Using CURRENT_SCHEMA
- Method 2: Using CURRENT_SCHEMAS
- Bonus Tip: Get All Schemas

How to Get the Name of the Current Schema in Postgres Via CURRENT_SCHEMA Function?

CURRENT_SCHEMA is a built-in Postgres function that retrieves the current schema’s name. It doesn’t accept any argument and can be executed with or without parenthesis. In the following code snippet, we execute the CURRENT_SCHEMA function with and without parenthesis:

SELECT CURRENT_SCHEMA, CURRENT_SCHEMA();

The output shows that the current schema name is “public”:

How to Get the Name of the Current Schema in PostgreSQL Using CURRENT_SCHEMAS Function?

CURRENT_SCHEMAS is a built-in Postgres function that retrieves the name of all schemas available on the currently valid search path. It retrieves the schemas with respect to their priority order. It accepts a Boolean value as its argument and returns the result accordingly. Passing the Boolean value “False” means excluding the implicit patterns while specifying the “True” value means including the implicit patterns.

Example 1: Using CURRENT_SCHEMAS With False Value

In the following snippet, we execute the CURRENT_SCHEMAS function with a boolean value “FALSE”:

SELECT CURRENT_SCHEMAS(FALSE);

Here is what the CURRENT_SCHEMAS() function retrieves on successful execution:

Example 2: Using CURRENT_SCHEMAS With True Value

In the following snippet, we execute the CURRENT_SCHEMAS function with a boolean value “TRUE”:

SELECT CURRENT_SCHEMAS(TRUE);

This time the stated function will retrieve all patterns (including implicit) on the currently valid path:

How to Get the Name of All Schemas in PostgreSQL?

Use the “\dn” meta-command to get the name of all schemas available in your Postgres database:

\dn

For more details on getting the list of schemas, check out the following guide.

Conclusion

In PostgreSQL, the “CURRENT_SCHEMA” and “CURRENT_SCHEMAS” functions are used to get the details of the current schema. The CURRENT_SCHEMA function retrieves the name of the current schema while the CURRENT_SCHEMAS function retrieves the name of all schemas available on the currently valid search path. Moreover, the “\dn” command can be used to get the names of all available schemas. This post has elaborated on various methods to get the name of the current schema in PostgreSQL.