Using psql to List Databases and Tables in PostgreSQL: A Beginner's Tutorial

Name

Rajiv Chandra

Published on 5/14/2023

(opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab)

Whether you're new to PostgreSQL or looking to sharpen your skills, this guide will walk you through the ins and outs of database management using psql, PostgreSQL's command-line interface. We'll explore everything from listing databases and tables to PostgreSQL admin tasks and user access.

PostgreSQL, or as the cool kids call it - 'Postgres', is an open-source relational database management system (RDBMS) known for its robustness, ACID compliance, and extensibility. One of the primary ways to interact with PostgreSQL is via the psql command line interface, a powerful tool offering a multitude of functions that can make your database interaction seamless and efficient. Let's start by understanding what it is.

Need to visualize your data? You can connect your database to RATH and instantly get AI-powered data insights.

Learn the advanced Data Analysis features of RATH and what RATH can do for your data!

What is psql command in PostgreSQL?

Psql is the interactive terminal for working with Postgres. It provides a prompt where you can enter SQL commands and psql meta-commands, displaying the results of your queries interactively. Notably, psql includes commands for tasks that SQL doesn't cover, like listing databases, tables, and managing your PostgreSQL connection.

How to use psql in PostgreSQL

The first step is to access PostgreSQL using psql. To do this, open your terminal and type psql. If you're connected, you'll see a prompt that looks like this: yourname=#. To quit, simply type \q and hit enter. Now that we're connected, let's get to the nitty-gritty, starting with how to list databases.

PostgreSQL psql list databases

To list databases in PostgreSQL, you'll use the \l or \list psql meta-command. It returns a list of all databases, alongside their owners, encoding, collate, ctype, and access privileges, offering a great overview of your PostgreSQL permissions. Here's how you do it:

yourname=# \l

This should return something like:

                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | yourname | UTF8     | en_US   | en_US | 
 template0 | yourname | UTF8     | en_US   | en_US | =c/yourname          +
           |          |          |         |       | yourname=CTc/yourname
 template1 | yourname | UTF8     | en_US   | en_US | =c/yourname          +
           |          |          |         |       | yourname=CTc/yourname
(3 rows)

How do I list tables in PostgreSQL using psql?

To list tables in your current database, the \dt command is your friend. This command will list all tables in the current database.

yourname=# \dt

This should return something like:

           List of relations
 Schema |   Name   | Type  |  Owner  
--------+----------+-------+---------
 public | my_table | table | yourname
(1 row)

From here, you can even list schemas with the \dn command, another essential part of PostgreSQL table management.

How do I switch between databases in PostgreSQL using psql?

Switching between databases is straightforward. Use the \c or \connect meta-command followed by the database name. For instance:

yourname=# \c my_database

You'll see a message like this:

You are now connected to database "my_database" as user "yourname".

Being able to switch between databases is a crucial part of managing tables and navigating complex PostgreSQL environments.

PostgreSQL User Access and Permissions

User access and permissions are fundamental to PostgreSQL. You can list users using the \du command:

yourname=# \du

And to list permissions, you can use the \dp command, which will show you access privileges for all tables in your current database.

PostgreSQL permissions are typically managed with SQL commands, but these psql meta-commands provide a handy overview.

PostgreSQL Admin Tasks and Table Management

Managing tables, creating new ones, and setting up your PostgreSQL database are all part of PostgreSQL admin tasks. If you want to create tables in PostgreSQL, the CREATE TABLE SQL command is what you need. However, for more complex tasks, tools like RATH and the Vector Database can be invaluable, offering visual interfaces and advanced functionalities.

Handy Tools: Psql Commands Cheat Sheet

For those interested in a comprehensive overview of psql commands, a psql commands cheat sheet can be a lifesaver. This ChatGPT Code Interpreter for Data Science provides an interactive way of learning psql commands and offers a wealth of other data science resources. You can also visualize your PostgreSQL data using tools like AirTable and Snowflake, or even Clickhouse and AWS.

Conclusion

PostgreSQL is a powerful, extensible, and robust RDBMS. With this guide, you should now be more comfortable interacting with it using psql commands, listing databases and tables, and managing user access. Remember that tools like Area Chart, Stacked Bar Charts, and Tableau can help visualize your PostgreSQL data and enhance your database management tasks. And if you're looking for alternatives to your current data visualization tool, be sure to check out these Chartio alternatives.

Happy PostgreSQL journey, and may the psql be with you!