DbSchema | How to Show Tables in PostgreSQL

---

Postgresql • 14-Apr-2020

by DbSchema Team

---

To show tables in PostgreSQL means to list all the tables that exist within a specific database. This is useful when you want to see what tables are available to work with. You can do this using built-in psql commands or by writing SQL queries that read from PostgreSQL’s system catalogs.

1.Using psql

PostgreSQL’s terminal tool (psql) provides several shortcuts for viewing tables. Here’s how to use them.

1.1 List tables from a specific database

To list all available databases from PostgreSQL, execute the next command:

\l

Then, select the database:

 \c database_name

To list all the tables execute:

 \dt

Output: This will return a list of all tables in the current schema. In PostgreSQL, these are referred to as relations—objects like tables or views that hold or represent data.

List of relations

Schema	Name	Type	Owner
public	customers	table	postgres
public	orders	table	postgres
public	products	table	postgres
public	order_items	table	postgres

1.2 List tables from all schemas

To show tables from all available schemas, execute the next command:

\dt *.*

1.3 List tables from a specific schema

To show only tables from a specific schema, execute:

\dt schema_name.*

Replace schema_name with the actual name of the schema (e.g., sales, organization, or public).

2.Using SQL Query

You can also list tables by querying PostgreSQL’s system catalog.

2.1 Show all tables:

SELECT * FROM pg_catalog.pg_tables;     

2.2 Show tables from a specific schema

Use a WHERE condition to filter by schema:

   SELECT * FROM pg_catalog.pg_tables
   WHERE schemaname = 'schema_name';
```    
Replace **'schema_name'** with the name of the schema you want. 

3.Using information_schema
-
   The `information_schema` is a standard SQL schema that stores metadata about the database structure. You can use it to list all tables in a PostgreSQL database, excluding system schemas like pg_catalog and information_schema.

To list all user-defined tables, run the following query:

SELECT table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

This will return all tables from schemas such as public, excluding internal PostgreSQL tables.

**Example Output:**

| table_name |
|------------|
| actor      |
| customer   |
| film       |
| rental     |
| payment    |
| staff      |