Payload Logo
Blog,  Development,  Education

List DB PostgreSQL – How to List Databases and Tables in PostgreSQL

Author

Naveed Ahmed

Date Published

list db postgresql

If you want to list db PostgreSQL, the fastest method is to use the \l command inside psql. You can also use psql -l from the terminal or run a SQL query against the pg_database catalog. For tables, PostgreSQL uses commands like \dt, \dt *.*, and SQL queries from information_schema.tables.

As a PostgreSQL consultant, I usually recommend learning both methods: psql commands for quick database checks and SQL queries for reporting, scripts, audits, and automation.

How to List DB PostgreSQL

To list databases inside psql, run:

1\l

You can also use:

1\list

To list databases from the terminal, run:

1psql -l

To list databases using SQL, run:

1SELECT datname FROM pg_database;

For most users, \l is the easiest command. For developers and database administrators, SQL queries provide more control.

What Does “List DB PostgreSQL” Mean?

The phrase list db PostgreSQL usually means you want to see all databases available on a PostgreSQL server. However, many users also use this phrase when they want to see tables inside a database.

That is an important difference.

A PostgreSQL server can contain multiple databases. Each database can contain multiple schemas. Each schema can contain tables, views, indexes, functions, and other objects.

So, before running commands, understand what you want to view:

  • Databases on the PostgreSQL server
  • Tables inside a selected database
  • Tables inside a specific schema
  • Columns, owners, permissions, or table sizes

PostgreSQL does not use MySQL-style commands like SHOW DATABASES; or SHOW TABLES; in the same way. Instead, PostgreSQL users commonly work with psql meta-commands and system catalog queries.

PostgreSQL List DB Using psql

The most common postgresql list db command is:

1\l

This command is used inside the psql terminal. It shows available databases with useful details such as database name, owner, encoding, collation, character type, and access privileges.

Example workflow:

1psql -U postgres

Then run:

1\l

You can also type:

1\list

Both commands return the database list.

psql List Databases from the Terminal

If you do not want to enter the interactive psql shell, use:

1psql -l

or:

1psql --list

This psql list databases command lists available databases and exits.

You can also include the username:

1psql -U postgres -l

For a local server with a specific host and port:

1psql -h localhost -p 5432 -U postgres -l

For a remote PostgreSQL server:

1psql -h your-hostname -p 5432 -U your_user -l

This is useful when checking database availability before deployment, migration, backup, or troubleshooting work.

PostgreSQL List Databases with SQL

postgresql list db

If you need a SQL-based method, use:

1SELECT datname FROM pg_database;

This returns the names of all databases visible to your user.

A cleaner version is:

1SELECT datnameFROM pg_databaseWHERE datistemplate = falseORDER BY datname;

This filters out template databases and shows normal databases only.

To show database owners, use:

1SELECT     d.datname AS database_name,    r.rolname AS ownerFROM pg_database dJOIN pg_roles r ON d.datdba = r.oidWHERE d.datistemplate = falseORDER BY d.datname;

This query is useful during audits because it shows which role owns each database.

Difference Between \l and SQL Query

Use \l when you are working manually inside psql.

Use a SQL query when you need structured output for a script, report, dashboard, or automation process.

Task

Best Option

Quick database check

\l

Terminal database list

psql -l

SQL-based database list

SELECT datname FROM pg_database;

Audit database owners

Query pg_database and pg_roles

Script-friendly output

SQL query

Both methods are valid. The right choice depends on how you are working.

psql List DB Command Examples

Here are practical psql list db examples.

List all databases

1\l

List databases with more details

1\l+

The + version shows extra details, such as database size and description when available.

List databases matching a pattern

1\l sales*

This can help when your PostgreSQL server has many databases, such as:

1sales_prodsales_devsales_test

List databases from shell

1psql -U postgres -l

List databases in plain output

1psql -U postgres -Atc "SELECT datname FROM pg_database WHERE datistemplate = false;"

This output is cleaner for scripts because it removes table borders and column headers.

How to Connect to a PostgreSQL Database

Before listing tables, you need to connect to the right database.

From the terminal:

1psql -U postgres -d your_database

Inside psql, switch databases with:

1\c your_database

To confirm your current database, run:

1SELECT current_database();

This simple check prevents a common mistake: listing tables in the wrong database.

psql List Tables

To list tables inside the connected database, run:

1\dt

This is the standard psql list tables command.

To show more detail:

1\dt+

The + version may show additional information such as table size and description.

List All Tables Postgres

To list all tables Postgres in the current search path, use:

1\dt

However, if tables are stored in another schema, \dt may not show them.

To list tables across all schemas, use:

1\dt *.*

To list tables in the public schema:

1\dt public.*

To list tables in a specific schema:

1\dt schema_name.*

Example:

1\dt reporting.*

To search for tables by name:

1\dt *customer*

This is helpful when you know part of a table name but not the full schema or naming structure.

List Database Tables Postgres with SQL

psql list databases

To list database tables Postgres using SQL, use:

1SELECT     table_schema,    table_nameFROM information_schema.tablesWHERE table_type = 'BASE TABLE'  AND table_schema NOT IN ('pg_catalog', 'information_schema')ORDER BY table_schema, table_name;

This query returns user tables and excludes system schemas.

It is useful for:

  • Database documentation
  • Migration planning
  • Data audits
  • Application reviews
  • BI and reporting tools
  • Automated checks

List Tables in Database psql

To list tables in database psql, follow this process:

First, connect to PostgreSQL:

1psql -U postgres

Then connect to the database:

1\c your_database

Now list tables:

1\dt

If you do not see any tables, run:

1\dt *.*

If tables appear after running \dt *.*, the issue was likely schema visibility.

PostgreSQL Show Tables in Database

PostgreSQL does not commonly use SHOW TABLES; like MySQL.

The PostgreSQL method is:

1\dt

or:

1SELECT table_schema, table_nameFROM information_schema.tablesWHERE table_type = 'BASE TABLE'ORDER BY table_schema, table_name;

For daily use, \dt is faster. For reusable SQL output, use the query.

List Tables with Schema Names

Schema names are important in PostgreSQL because two schemas can contain tables with the same name.

Use this query:

1SELECT     schemaname,    tablenameFROM pg_tablesWHERE schemaname NOT IN ('pg_catalog', 'information_schema')ORDER BY schemaname, tablename;

Example output:

1public.customerspublic.ordersreporting.monthly_revenueaudit.login_events

This format gives a clearer view of your database structure.

List Tables with Estimated Row Counts

For a quick table size review by row count, use:

1SELECT     schemaname,    relname AS table_name,    n_live_tup AS estimated_rowsFROM pg_stat_user_tablesORDER BY n_live_tup DESC;

This gives estimated row counts, not exact counts.

For an exact count, use:

1SELECT COUNT(*) FROM table_name;

Be careful with exact counts on very large tables. They can take time and add load to production systems.

List Tables with Table Size

To list tables by size, use:

1SELECT    schemaname,    relname AS table_name,    pg_size_pretty(pg_total_relation_size(relid)) AS total_sizeFROM pg_catalog.pg_statio_user_tablesORDER BY pg_total_relation_size(relid) DESC;

This query is useful before:

  • Database cleanup
  • Migration planning
  • Performance reviews
  • Backup checks
  • Storage optimization
  • Index analysis

Inside psql, you can also run:

1\dt+

This provides a quick table-size view.

List Columns in PostgreSQL Tables

To view columns for a table, use:

1\d table_name

For a schema-qualified table:

1\d public.customers

For SQL output, use:

1SELECT     table_schema,    table_name,    column_name,    data_typeFROM information_schema.columnsWHERE table_schema NOT IN ('pg_catalog', 'information_schema')ORDER BY table_schema, table_name, ordinal_position;

This is helpful when preparing documentation, mapping data, or planning migrations.

Common PostgreSQL Listing Commands

Here is a practical PostgreSQL command cheat sheet.

Goal

Command

List databases inside psql

\l

List databases with more detail

\l+

List databases from terminal

psql -l

List databases using SQL

SELECT datname FROM pg_database;

Connect to database

\c database_name

Show current database

SELECT current_database();

List tables

\dt

List all tables across schemas

\dt *.*

List tables in public schema

\dt public.*

List schemas

\dn

Describe a table

\d table_name

Describe table with more detail

\d+ table_name

List privileges

\dp

Common Errors When Listing PostgreSQL Databases and Tables

psql: command not found

This means the PostgreSQL client is not installed or not available in your system path.

Check with:

1psql --version

If the command fails, install the PostgreSQL client tools.

FATAL: database does not exist

This means the database name is wrong or the database has not been created.

List available databases:

1psql -U postgres -l

Then connect using the correct name:

1psql -U postgres -d correct_database_name

No relations found

This message usually means psql cannot find tables in your current schema search path.

Try:

1\dt *.*

Also check available schemas:

1\dn

Then list tables in a specific schema:

1\dt schema_name.*

Tables are missing because of permissions

If your PostgreSQL role does not have access to a table or schema, you may not see it in some listings.

Check privileges with:

1\dp

You can also ask a database administrator to review your role permissions.

Best Consultant Workflow for PostgreSQL Database Review

For a clean PostgreSQL review, follow this order:

  1. List databases:
1\l
  1. Connect to the right database:
1\c database_name
  1. Confirm the current database:
1SELECT current_database();
  1. List schemas:
1\dn
  1. List tables across schemas:
1\dt *.*
  1. Review table structure:
1\d schema_name.table_name
  1. Check table sizes:
1\dt+

This workflow is simple, but it prevents many common mistakes during database checks, migrations, backups, and troubleshooting.

When to Use psql Commands vs SQL Queries

Use psql commands when you are working manually.

Examples:

1\l\dt\dt *.*\d customers

Use SQL queries when you need exportable results.

Examples:

1SELECT datname FROM pg_database;
1SELECT table_schema, table_nameFROM information_schema.tablesWHERE table_type = 'BASE TABLE';

The difference is simple. psql commands are faster for humans. SQL queries are better for systems, scripts, dashboards, and reports.

Conclusion – Postgres List DB

To list db PostgreSQL, use \l, \list, psql -l, or SELECT datname FROM pg_database;. To list tables, connect to the correct database and use \dt, \dt *.*, or a SQL query from information_schema.tables.

For quick checks, psql commands are the fastest. For scripts, reporting, and audits, SQL queries give better control. A clean PostgreSQL review starts with listing databases, connecting to the right database, checking schemas, and then listing tables with schema-qualified commands.

Relevant Guides

How to Use AI to Automate Tasks

CRM Software for Non-Profit Organizations

Catering Delivery Driver App

Apps for Enterpreneurs

Multimodal Communication