A Hands-On Guide to Listing Databases and Tables in Postgres

PostgreSQL, otherwise known as Postgres, is an open-source object-relational database system that enables you to store and scale complex data workloads safely. It uses and extends SQL, the language that enables users to access databases.

To manage a Postgres database server effectively, you must be familiar with your environment and what the server stores. Sometimes, you may need to list the databases running on your server, retrieve information about them, and connect to them. You can complete these tasks using the PostgreSQL interface or a graphical tool like Adminer to easily view and manipulate this information.

If your database is just one of several on the Postgres server, you may need to filter the list to find it. Then, you’ll often need an overview of the data it contains, requiring a list of its tables. This hands-on guide demonstrates how to list databases and tables in Postgres.

Prerequisites

To follow this guide, ensure you have the following:

• A Postgres database server with one or more databases installed.
• Superuser access credentials for an account on the database server.
• Familiarity with basic SQL commands, such as SELECT statements and WHERE clauses.

You also need to have some databases and tables available.

How To Connect to PostgreSQL Database Server

Connecting to a PostgreSQL database server involves accessing its unique SQL version, called psql, through the SQL Shell available on your desktop post-installation.

Using psql and SQL Shell, you can manage your database environment. While most SQL commands manipulate data and tables, psql meta-commands handle database administration tasks. For instance, create or delete databases using meta-commands, and add tables or retrieve data with SQL commands.

Psql meta-commands are distinguished by a backslash (/) prefix, with many having shorter versions. For instance, both /help and /h perform the same function.

To execute SQL statements and meta-commands, log in to your server through SQL Shell. You’ll be prompted to provide:

• The target database name
• Your username
• Your password

After you enter your information, a prompt appears, enabling you to enter SQL commands and meta-commands:

Once you’ve connected, you can:

• List available databases
• Connect to a specific database
• Filter the database list
• Switch between databases
• List tables within a database

How To Show Databases

Within your Postgres server, managing multiple databases is standard practice. Each database resides in a unique directory, stored separately within the server.

To manage your database server effectively, you may need to retrieve specific information, like a list of the server’s databases and their relevant details. To list the databases in your Postgres server using psql, log in to your Postgres environment in SQL Shell.

Once logged in, input the meta-command /list at the command line. This prompts SQL Shell to display an overview of databases present in the environment, as in the image below:

For a quicker command, you can use the abbreviated form /l, yielding the same comprehensive database listing.

Filtering Databases in a Postgres Environment

When navigating through an environment with numerous databases, pinpointing a specific one becomes essential. SQL Shell allows you to achieve this by incorporating database characteristics into your command.

In Postgres environments, database information is stored within the pg_database table. For instance, if you aim to retrieve and filter databases containing the term “test,” execute the following query:

SELECT * FROM pg_database WHERE datname = 'test_data';

SQL Shell then lists the databases that correspond to your criteria:

Because this method of listing databases and filtering the results is based on SQL, you can construct complex queries to create filtered lists of databases. However, this requires familiarity with SQL and Postgres metadata.

How To Connect to and Switch Databases

In navigating databases using SQL Shell and PSQL, connecting to or switching between databases is a straightforward process, provided you know the target database’s name.

To connect or switch to a database, log in to your Postgres environment in SQL Shell.

Then, at the command line, enter the meta-command /connect, followed by the name of the destination database. For example, to connect with a database named test_data, use the command /connect test_data.

SQL Shell displays the following message after connecting to the database:

Alternatively, you can use the short command /c to achieve the same results.

How To List Database Tables

Once you’ve connected to a database, you can list the tables it contains. To do this, enter the /dt command.

SQL Shell lists all the tables in the database:

Accessing Databases and Tables With Adminer

SQL Shell is just one tool for administering your Postgres databases. Another tool is Adminer, which provides a simple graphical user interface (GUI) to manage your database server. Additionally, Adminer works in any environment where you have installed PHP.

You could install and run Adminer independently, but it’s also one of many features in the DevKinsta suite.

Once you have installed Adminer, run it and enter the credentials for your Postgres environment:

You can use Adminer to retrieve a list of databases in your Postgres environment. When you log in to your database server, a list of its databases appears:

To connect to your desired database, select it from the list. Adminer then displays a list of the tables, views, routines, sequences, and user types for the database you selected.

Alternatively, you can select a database from the DB drop-down on the left-hand side of the page:

Summary

To successfully manage the databases in your Postgres environment, you need to be able to view, filter, and interact with the contained data with minimal effort. You might opt for the SQL Shell command line, but the ease of a GUI tool like Adminer may fit into your workflow more seamlessly.

For WordPress developers, DevKinsta is a powerful ally, offering access to Adminer alongside a suite of other tools designed to streamline database management. To easily manage your Postgres databases, check out DevKinsta — it’s free forever!

The post A Hands-On Guide to Listing Databases and Tables in Postgres appeared first on Kinsta®.