Use psql to List Schemas for a Postgres Database
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 -Vto verify that it’s installed and get the version number of the installation.You’ll need to have access to the
psqlcommand-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