List DB PostgreSQL – How to List Databases and Tables in 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:

\l

You can also use:

\list

To list databases from the terminal, run:

psql -l

To list databases using SQL, run:

SELECT 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:

\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:

psql -U postgres

Then run:

\l

You can also type:

\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:

psql -l

or:

psql --list

This psql list databases command lists available databases and exits.

You can also include the username:

psql -U postgres -l

For a local server with a specific host and port:

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

For a remote PostgreSQL server:

psql -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

If you need a SQL-based method, use:

SELECT datname FROM pg_database;

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

A cleaner version is:

SELECT datname
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;

This filters out template databases and shows normal databases only.

To show database owners, use:

SELECT 
    d.datname AS database_name,
    r.rolname AS owner
FROM pg_database d
JOIN pg_roles r ON d.datdba = r.oid
WHERE d.datistemplate = false
ORDER 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.

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

\l

List databases with more details

\l+

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

List databases matching a pattern

\l sales*

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

sales_prod
sales_dev
sales_test

List databases from shell

psql -U postgres -l

List databases in plain output

psql -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:

psql -U postgres -d your_database

Inside psql, switch databases with:

\c your_database

To confirm your current database, run:

SELECT 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:

\dt

This is the standard psql list tables command.

To show more detail:

\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:

\dt

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

To list tables across all schemas, use:

\dt *.*

To list tables in the public schema:

\dt public.*

To list tables in a specific schema:

\dt schema_name.*

Example:

\dt reporting.*

To search for tables by name:

\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

To list database tables Postgres using SQL, use:

SELECT 
    table_schema,
    table_name
FROM information_schema.tables
WHERE 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:

psql -U postgres

Then connect to the database:

\c your_database

Now list tables:

\dt

If you do not see any tables, run:

\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:

\dt

or:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE 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:

SELECT 
    schemaname,
    tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

Example output:

public.customers
public.orders
reporting.monthly_revenue
audit.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:

SELECT 
    schemaname,
    relname AS table_name,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

This gives estimated row counts, not exact counts.

For an exact count, use:

SELECT 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:

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER 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:

\dt+

This provides a quick table-size view.

List Columns in PostgreSQL Tables

To view columns for a table, use:

\d table_name

For a schema-qualified table:

\d public.customers

For SQL output, use:

SELECT 
    table_schema,
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE 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.

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:

psql --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:

psql -U postgres -l

Then connect using the correct name:

psql -U postgres -d correct_database_name

No relations found

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

Try:

\dt *.*

Also check available schemas:

\dn

Then list tables in a specific schema:

\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:

\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:

\l

2. Connect to the right database:

\c database_name

3. Confirm the current database:

SELECT current_database();

4. List schemas:

\dn

5. List tables across schemas:

\dt *.*

6. Review table structure:

\d schema_name.table_name

7. Check table sizes:

\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:

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

Use SQL queries when you need exportable results.

Examples:

SELECT datname FROM pg_database;

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';

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

FAQ – List DB PostgreSQL

How do I list db PostgreSQL?

Use \l or \list inside psql. From the terminal, use psql -l. With SQL, use SELECT datname FROM pg_database;.

What is the psql list databases command?

The main psql list databases command is:

\l

You can also use:

\list

How do I list databases in Postgres?

To list databases in Postgres, run:

psql -l

or connect to psql and run:

\l

How do I list all tables Postgres?

Connect to the database and run:

\dt

To list tables across schemas, run:

\dt *.*

How do I list database tables Postgres using SQL?

Use:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

How do I list tables in psql for one schema?

Use:

\dt schema_name.*

Example:

\dt public.*

Why does PostgreSQL not show my tables?

You may be connected to the wrong database, looking at the wrong schema, or using a role without the right permissions. Check the current database, list schemas, and run \dt *.*.

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