How to List PostgreSQL Databases and Tables using PSQL

Introduction

Before we begin talking about how to list PostgreSQL Databases and Tables using PSQL, let's briefly understand – What is PostgreSQL?

Listing databases and their tables is a common task when administering PostgreSQL database servers.

psql, an interactive tool included with PostgreSQL, allows you to connect to the server and execute queries. It also offers meta-commands, also known as backslash commands, which begin with an unquoted backslash. These commands are useful for scripting and command-line administration.

In this tutorial, you will list PostgreSQL Databases and Tables using PSQL. We will also address a few FAQs on how to list PostgreSQL Databases and Tables using PSQL.

List PostgreSQL Databases

The psql command allows you to connect as any system user to the PostgreSQL server. Depending on the server settings, the user may be required to enter their password to access the psql terminal. To do so, simply type psql.

An administrator user called "postgres" is created when the PostgreSQL package is installed. By default, this user does not need a password to connect to the local PostgreSQL server.

To access the psql terminal as user "postgres", run the following command:

sudo -u postgres psql

💡

You can run commands as another user by using the sudo command.

To list all databases, enter the \l or \list meta-command in the psql terminal:

\l

The output will show the total number of databases, their names, owners, encodings, and access privileges:

Output

                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 odoo      | odoo     | UTF8     | C       | C.UTF-8 | 
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

Three databases - template0, template1, and postgres - are generated by default on the PostgreSQL server. When building new databases, the first two are used as templates.

Use \l+ or \list+ to obtain information on database sizes, default tablespaces, and descriptions. The database size is only displayed if the current user can establish a connection.

Use the -c switch as indicated below to receive a list of all databases without using the psql shell:

sudo -u postgres psql -c "\l"

Using the following SQL command is another method of listing the databases:

SELECT datname FROM pg_database;

The query above will simply display the databases' names, unlike the \l meta-command:

Output

  datname  
-----------
 postgres
 odoo
 template1
 template0
(4 rows)

List PostgreSQL Tables

You must first connect to a specific database using the \c or \connect meta-command in order to list all of its tables. You must be able to connect to the database as the user you are now logged in as in the psql terminal.

To connect to the database "odoo," for example, you would type:

\c odoo

Use the \dt meta-command to list all database tables after switching databases:

The output will provide the total number of tables, their names, schemas, types, and owners:

Output

                              List of relations
 Schema |                        Name                         | Type  | Owner 
--------+-----------------------------------------------------+-------+-------
 public | base_import_import                                  | table | odoo
 public | base_import_mapping                                 | table | odoo
 public | base_import_tests_models_char                       | table | odoo
 ...
 public | web_editor_converter_test_sub                       | table | odoo
 public | web_tour_tour                                       | table | odoo
 public | wizard_ir_model_menu_create                         | table | odoo
(107 rows)

This is what the output will look like if the database is empty:

Output

No relations found.

Use \dt+ to obtain information about the table sizes and descriptions.

FAQs to List PostgreSQL Databases and Tables using PSQL

Is there a way to display more detailed information about the tables using PSQL?

Yes, you can use the \d+ <table_name> command to display more detailed information about a specific table, including columns, data types, constraints, and indexes.

Can I list only the tables within a specific schema?

Yes, you can use the following command: \dt <schema_name>.* to list all the tables within a specific schema.

How can I get a summary of all the tables in the current database?

You can use the \d command to get a summary of all the tables in the current database. It will provide basic details like table names and corresponding schemas.

Is it possible to view the CREATE TABLE statements for a specific table in PSQL?

Yes, you can view the CREATE TABLE statement for a specific table by using the \d+ <table_name> command. It will display the complete table definition including the CREATE TABLE statement.

What is the command to see the indexes on a specific table using PSQL?

To view the indexes on a specific table, you can use the \di <table_name> command.

How can I list all the foreign keys of a specific table in PSQL?

You can use the \d <table_name> command to list all the columns of a specific table, including foreign keys and their references.

How do I check the size of a specific table in PostgreSQL using PSQL?

To check the size of a specific table, you can use the following command: \dt+ <table_name>. It will display the size information along with other details.