Guide to Listing Databases and Tables in PostgreSQL

Posted on Latest In Blogs
318

PostgreSQL, a powerful open-source relational database system, offers extensive functionality for managing databases and tables. Knowing how to list all databases and tables is fundamental for database administrators and developers. This guide will walk you through the steps and commands needed to list databases and tables in PostgreSQL.

Prerequisites

Before you begin, ensure you have:

  • PostgreSQL installed and running on your system.
  • Access to the PostgreSQL server with appropriate permissions.
  • A PostgreSQL client such as psql for executing commands.

Listing Databases

To list all databases in your PostgreSQL server, you can use the psql command-line tool or connect via a PostgreSQL client.

Using psql Command-Line Tool

  1. Open the terminal and start the psql command-line tool by typing:
    sh
    psql -U your_username

    Replace your_username with your PostgreSQL username. If you’re connecting to a remote server, you might also need to specify the host and port:

    sh
    psql -h your_host -p your_port -U your_username
  2. List all databases by executing the following command:
    sql
    \l

    Alternatively, you can use:

    sql
    \list

Example Output

plaintext

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
mydb | myuser | UTF8 | en_US.utf8 | en_US.utf8 |
(4 rows)

Listing Tables in a Specific Database

To list all tables in a specific database, you first need to connect to that database.

Using psql Command-Line Tool

  1. Connect to the database:
    sh
    psql -U your_username -d your_database

    Replace your_username with your PostgreSQL username and your_database with the name of the database you want to connect to.

  2. List all tables by executing the following command:

    sql

    \dt

    This command will list all tables in the current schema.

Example Output

plaintext

List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | employees | table | myuser
public | departments | table | myuser
public | salaries | table | myuser
(3 rows)

Listing Tables in All Schemas

If you want to list tables across all schemas, use:

sql

\dt *

Filtering Tables

To filter tables by a specific pattern, you can use:

sql

\dt pattern*

For example:

sql

\dt emp*

This will list all tables whose names start with emp.

Alternative Method: Using SQL Queries

Listing Databases

You can also list databases using an SQL query:

sql

SELECT datname FROM pg_database;

Listing Tables

To list all tables in the current database using an SQL query:

sql

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

This query will list all user-defined tables, excluding system tables.

Conclusion

Listing databases and tables in PostgreSQL is a straightforward task once you are familiar with the psql tool and the appropriate SQL queries. Whether you are using built-in psql commands or SQL queries, PostgreSQL provides flexible options to view and manage your database structure effectively. By following this guide, you should be able to navigate and manage your PostgreSQL databases and tables with ease.