Use psql to List Schemas for a Postgres Database

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In a SQL database, a schema allows you to organize objects in the database into logical groups. If you’re a PostgreSQL database administrator, you may want to view a list of schemas that exist in your database. Fortunately, it’s easy to get this information with the help of the psql command-line interface for PostgreSQL. In this article, we’ll explain how to use psql to list schemas for a PostgreSQL database.

Prerequisites

In order to proceed with this tutorial, a few key prerequisites should be in place:

  • You’ll need to have PostgreSQL installed on your machine. You can use the command psql -V to verify that it’s installed and get the version number of the installation.

  • You’ll need to have access to the psql command-line interface for PostgreSQL.

  • It’s helpful to have some knowledge of database management systems and SQL commands.

Install Postgres and psql

If your machine has a Debian-based distribution of Linux such as Linux Mint or Ubuntu, you can install PostgreSQL using the APT-GET repository.

Just use the following command in the terminal:

1
sudo apt update && sudo apt install postgresql postgresql-contrib -y

NOTE: The -y flag automatically passes a yes response when the installation requests approval to continue.

Install Postgres on macOS

You can use Homebrew’s brew command to install PostgreSQL on macOS. Use the command shown below to get the latest update from its package:

1
brew update

Then install PostgreSQL with the following command:

1
brew install postgresql

You can install PostgreSQL on Windows by downloading the interactive installer and choosing the 32-bit or 64-bit Windows version.

Enter the psql command line

Before you can list schemas in PostgreSQL, you’ll need to have access to the psql console.

To enter the psql command-line interface as the postgres superuser, enter the following command:

1
sudo su - postgres

You can then start the interactive terminal for PostgreSQL using the command below:

1
psql

List schemas in PostgreSQL

A schema can be defined as a set of views which contain a current database objects such as tables, operators, indexes, views, data types and functions.

We can list schemas using a SELECT statement:

1
postgres=# SELECT schema_name FROM information_schema.schemata;

This will return the list from the information_schema name using the ANSI standard:

1
2
3
4
5
6
7
8
9
    schema_name    
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 public
 information_schema
(6 rows)

Here’s another example that uses a SELECT statement to list schemas:

1
2
3
4
postgres=# SELECT s.nspname AS schema_table,
    s.oid AS id_schema, u.usename AS ROLE
FROM pg_catalog.pg_namespace s JOIN pg_catalog.pg_user u
ON u.usesysid = s.nspowner ORDER BY schema_table;

The output will look like the following:

1
2
3
4
5
6
7
8
9
    schema_table    | id_schema |   role  
--------------------+-----------+----------
 information_schema |     13132 | postgres
 pg_catalog         |        11 | postgres
 pg_temp_1          |     12314 | postgres
 pg_toast           |        99 | postgres
 pg_toast_temp_1    |     12315 | postgres
 public             |      2200 | postgres
(6 rows)

NOTE: This example illustrates an alternative way to display the schema with details using the id_schema and the role.

There’s also a simple command in psql that will show the schema for the current database. The syntax for the command is:

1
2
3
4
5
6
postgres=# \dn
  List OF schemas
  Name  |  Owner  
--------+----------
 public | postgres
(1 ROW)

To return and display the list of schemas for a user, use the following command:

1
2
3
4
5
6
7
8
postgres=# SELECT s.nspname AS schema_table,
    s.oid AS id_schema, u.usename AS ROLE
FROM pg_catalog.pg_namespace s JOIN pg_catalog.pg_user u
ON u.usesysid = s.nspowner WHERE nspname
    NOT IN ('information_schema', 'pg_catalog', 'public')
    AND nspname NOT LIKE 'pg_toast%'
    AND nspname NOT LIKE 'pg_temp%'
ORDER BY schema_table;

Conclusion

If you’re a PostgreSQL database administrator, it’s important to know how to retrieve the information you need regarding your database. One type of information you may need is a list of schemas. In this article, we showed you how to use psql to list schemas for a Postgres database, and we looked at multiple examples that illustrate different ways to get the information. With our examples to get you started, you should be prepared to list schemas in your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.