How to List PostgreSQL Databases and Tables using psql

When working on a PostgreSQL server, you will often need to see which databases exist and which tables are stored in each one.

PostgreSQL includes an interactive terminal program called psql. Along with running SQL queries, psql also supports meta-commands such as \l to list databases and \dt to list tables. All meta-commands begin with an unquoted backslash.

This tutorial explains how to list PostgreSQL databases and tables using psql.

Listing Databases #

You can connect to the PostgreSQL server using the psql command as any system user. Depending on the server configuration, the user may need to enter its password to connect to the psql terminal. To access the psql terminal as the user you are currently logged in, simply type psql.

When the PostgreSQL package is installed, an administrative user named “postgres” is created. By default, this user can connect to the local PostgreSQL server without a password.

To access the psql terminal as user “postgres”, run:

sudo -u postgres psql

From within the psql terminal execute \l or \list meta-command to list all databases:

\l

The output will include the number of databases, name of each database, its owner, encoding and access privileges:

                              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)

PostgreSQL server has three databases created by default, template0, template1, and postgres. The first two are templates that are used when creating new databases.

If you want to get information about the sizes of the databases, default tablespaces, and descriptions use \l+ or \list+. The database size is shown only if the current user can connect to it.

To get a list of all databases without accessing the psql shell, use the -c switch as shown below:

sudo -u postgres psql -c "\l"

Another way to list the databases is to use the following SQL statement:

SELECT datname FROM pg_database;

Unlike the \l meta-command the query above will show only the names of the databases:

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

Listing Tables #

To list all the tables of a particular database first, you need to connect to it using the \c or \connect meta-command. The user you are logged in as to the psql terminal must be able to connect to the database.

For example, to connect to the database named “odoo” you would type:

\c odoo

Once the database is switched, use the \dt meta-command to list all database tables:

\dt

The output will include the number of the tables, the name of each table and its schema, type, and owner:

                              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)

If the database is empty, the output will look like this:

No relations found.

To get information about the sizes of the tables, and descriptions use \dt+.

Describe a Table #

To inspect the columns, types, and constraints of a specific table, use the \d meta-command followed by the table name:

\d table_name

This command shows the table columns, their data types, and other details such as indexes or constraints when they are defined.

For full detail including table size and access privileges, use \d+ table_name.

Listing Tables in a Specific Schema #

By default, \dt lists tables in the search path. To list tables in a specific schema, pass the schema name with a wildcard:

\dt schema_name.*

For example, \dt public.* shows only tables in the public schema.

To list all schemas in the current database, use \dn.

You can also query the information_schema.tables view directly:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema');

Quick Reference #

Conclusion #

You have learned how to list PostgreSQL databases and tables from the psql shell with meta-commands and equivalent SQL queries. For related tasks, see our guides on checking the PostgreSQL version and PostgreSQL user management .

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page