PostgreSQL database management system provides a variety of features and options for storing and organizing data. However, “with great power comes great responsibility”. For instance, While working with Postgres, you need to deal with tables, schemas, views, users, etc. Therefore keeping track of all the different elements in a database is critical.

Step-by-step instructions are provided in this blog for listing users, databases, schemas, and tables in PostgreSQL. The content of this blog is organized as follows:

• PostgreSQL List Users/Roles.
• PostgreSQL List Databases.
• PostgreSQL List Schemas.
• PostgreSQL List Tables.

Let’s start with the Postgres users.

PostgreSQL List Users/Roles

In Postgres, the “pg_catalog”, “\du”, and “\du+” commands are used to find the list of all users. The “\du” and “\du+” command must be executed from the SQL Shell.

Let’s learn how the “\du” and “\du+” commands work in Postgres:

\du;

The “\du” command retrieves the details regarding the list of roles, such as “role/user name”, “attributes”, and “member of”. The “\du+” command retrieves all these details along with the “description”:

\du+;

For more detailed information, such as the user’s sysid, superuser, etc., you can use the “pg_user” table. To get the list of users/roles via the pg_catalog, you need to execute the below-provided command:

SELECT * 
FROM pg_catalog.pg_user;

Note: This command can be executed from any interface, such as psql or pgAdmin:

PostgreSQL List Databases

Use the “\l”, “\l+” commands or the “pg_database” catalog to get the list of all the available databases in a current server. The “\l” and “\l+” commands must be executed from the SQL Shell; however, the “pg_database” catalog can be used with the help of the SELECT statement from any interface, like pgAdmin or psql.

\l;

Here, template0 and template1 are the standard databases, while the remaining are user-defined databases.

Note: Use the “\l+” command to get more details regarding the available databases, such as database size, description, etc.

Let’s learn how to get the list of Postgres databases via the “pg_database” catalog:

SELECT oid, datname
FROM pg_database;

You can get more details regarding the databases, such as connection limit, tablespace, encoding, etc., by specifying the respective columns’ names in the SELECT statement.

PostgreSQL List Schemas

In Postgres, the “\dn”, “\dn+” commands, and “information_schema” are used to get the list of available schemas. The “\dn” and “\dn+” commands must be executed from the SQL Shell; however, getting the list of available schemas using “information_schema” can be accomplished from any interface like pgAdmin or psql.

Let’s practice the “\dn” and “dn+” commands via the SQL Shell:

\dn;

Use the “\dn+” command for more details regarding schemas, such as access privileges or description:

\dn+;

Use the SELECT command to fetch the schema’s information from the “information_schema”:

SELECT schema_name, schema_owner
FROM information_schema.schemata;

This way, you can find the list of available schemas in Postgres.

PostgreSQL List Tables

Users need to utilize either the “pg_catalog.pg_tables” or the “\dt” command to find the list of available tables. You can also use the “\d” and “\dt+” commands from the SQL Shell to get the list of available tables. However, the “\dt+” command will show the list of all the tables with more details like table size, access method, etc. While the “\d” command will show the list containing tables, views, sequences, etc.

Let’s implement the “\dt” command via the SQL Shell:

\dt;

The below-provided command explains how to use the “pg_catalog.pg_tables” to get the list of all available relations/tables:

SELECT tablename 
FROM pg_catalog.pg_tables;

To get only user-defined tables, you need to execute the following command:

SELECT tablename 
FROM pg_catalog.pg_tables
WHERE schemaname!='information_schema' AND schemaname!= 'pg_catalog';

This time, the “pg_catalog.pg_tables” retrieves the user-defined tables only.

Conclusion

Postgres provides different built-in commands to find the list of users, databases, schemas, or tables. For instance, the “\du”, “\l”, “\dn”, and “\dt” commands are used to find the list of users, databases, schemas, and tables, respectively. “pg_catalog” and “information_schema” can also be used to find the list of users, databases, schemas, and tables in Postgres. This post has demonstrated how to list users, schemas, databases, and tables in PostgreSQL via different built-in commands, schemas, and catalogs.