How to Show Tables in PostgreSQL

How to Show Tables in PostgreSQL with Examples?

In PostgreSQL, there are several ways to display all tables within a database. Here’s a comprehensive guide on how to list tables using SQL commands and the psql command-line interface.

1. Using \dt Command in psql

The \dt command is used in PostgreSQL's interactive terminal, psql, to display tables within the connected database.

Syntax:

\dt

Example Code:

-- Connect to the psql command line
\c database_name 

-- List all tables
\dt

Explanation:

• \c database_name: Connect to the desired database (replace database_name with the actual name of the database).
• \dt: Lists all tables within the connected database, including details like schema, name, type, and owner.

2. Using SQL Query on information_schema

PostgreSQL stores metadata about database objects in the information_schema tables. You can use a SQL query to get a list of all tables.

Syntax:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

Example Code:

-- Retrieve table names from the 'public' schema
SELECT table_name              -- Select the 'table_name' column
FROM information_schema.tables -- From the 'tables' information schema
WHERE table_schema = 'public'; -- Where schema is 'public'

Explanation:

• table_name: Specifies the column that holds the names of tables.
• information_schema.tables: A schema containing metadata about tables in the database.
• WHERE table_schema = 'public': Filters to show only tables within the public schema (change this to target different schemas if necessary).

3. Using pg_catalog.pg_tables

Another way to list tables is by querying the pg_catalog.pg_tables system catalog.

Syntax:

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

Example Code:

-- Retrieve table names from 'public' schema
SELECT tablename               -- Select the 'tablename' column
FROM pg_catalog.pg_tables      -- From 'pg_tables' catalog
WHERE schemaname = 'public';   -- Where schema is 'public'

Explanation:

• tablename: Column containing table names.
• pg_catalog.pg_tables: System catalog containing information about tables.
• WHERE schemaname = 'public': Restricts the output to the public schema.