How to Show Databases in PostgreSQL

Key Takeaways

1. Use \l or \l+ commands in psql to list databases.
2. Query the pg_database system catalog to view databases via SQL.
3. Graphical tools like pgAdmin and DBeaver provide an easier visual option.

Unlike MySQL, PostgreSQL does not support the SHOW DATABASES SQL command. However, there are several effective methods to list all databases on a PostgreSQL server, whether you're using the command-line interface, SQL queries, or graphical tools. (PostgreSQL – Show Databases | GeeksforGeeks)

---

1. Using the psql Command-Line Interface

The psql tool is PostgreSQL's interactive terminal. After connecting to your PostgreSQL server, you can list all databases using the following meta-commands: (Documentation: 17: 1.4. Accessing a Database - PostgreSQL, How to List databases and tables in PostgreSQL using psql - Atlassian)

• List all databases:

  \l

This command displays a list of all databases along with details such as their owners, encoding, collation, and access privileges. (How to List Databases Using the psql command line tool)

• List all databases with additional information:

  \l+

This provides extra details, including the size of each database and any associated comments. (How to List Databases Using the psql command line tool)

Alternatively, from your system's terminal, you can execute:

psql -U <username> -l

Replace <username> with your PostgreSQL username. This command connects to the server and lists all databases. (How To List Databases in PostgreSQL: A Complete Guide)

---

2. Using SQL Queries

You can also retrieve a list of databases by querying the pg_database system catalog:

SELECT datname FROM pg_database WHERE datistemplate = false;

This SQL statement returns the names of all databases, excluding the default template databases (template0 and template1). (How to List databases and tables in PostgreSQL using psql - Atlassian)

---

3. Using Graphical Tools

For those who prefer graphical interfaces, tools like pgAdmin, DBeaver, and DbVisualizer offer user-friendly ways to view databases: (Three Easy Ways to List Databases in PostgreSQL - Medium)

• pgAdmin:

  1. Open pgAdmin and connect to your PostgreSQL server.
  2. In the left-hand tree, expand the "Servers" node.
  3. Under your server, expand the "Databases" node to see all available databases. (How To List Databases in PostgreSQL: A Complete Guide, How to List All Databases in PostgreSQL (psql and More) - StrongDM)

• DBeaver:

  1. Launch DBeaver and create a new database connection.
  2. Select "PostgreSQL" as the database type.
  3. Enter your connection details and enable the "Show all databases" option.
  4. Once connected, expand the "Databases" node to view all databases. (How to List All Databases in PostgreSQL (psql and More) - StrongDM, How to List All Databases in PostgreSQL {3 Methods} - phoenixNAP)

• DbVisualizer:

  1. Open DbVisualizer and connect to your PostgreSQL server.
  2. In the "Databases" tab, you'll see a list of all databases on the server. (How to List All Databases in PostgreSQL {3 Methods} - phoenixNAP)

---

Summary

• Use \l or \l+ in psql to list all databases.
• Execute SELECT datname FROM pg_database WHERE datistemplate = false; to retrieve database names via SQL.
• Utilize graphical tools like pgAdmin, DBeaver, or DbVisualizer for a visual representation of your databases. (PostgreSQL Show Databases - Neon, How to List All Databases in PostgreSQL (psql and More) - StrongDM)

By employing these methods, you can effectively view and manage the databases within your PostgreSQL server.

FAQs

What is the quickest way to list all databases in PostgreSQL?

Use the \l or \l+ command inside the psql terminal.

Can I list databases without using the command line?

Yes, graphical tools like pgAdmin allow you to view databases easily.

Is there a SQL query to show databases in PostgreSQL?

Yes, you can run SELECT datname FROM pg_database WHERE datistemplate = false;.

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ