Listing tables in PostgreSQL Schema

How to list tables in Schema in PostgreSQL?

In PostgreSQL, tables can be organized within schemas, which act as namespaces for database objects. Listing tables within these schemas helps users navigate and manage databases, especially when multiple schemas are in use.

Description

PostgreSQL provides several commands to list all tables within specific schemas. Using psql commands and SQL queries, you can quickly display a complete table list, filtered by schema if needed.

Syntax:

To list tables in PostgreSQL schemas, you can use the following methods:

1. psql Meta-command for All Tables:

\dt schema_name.*

Usage: This command is used within the PostgreSQL psql command-line interface.

2. Query Using information_schema.tables:

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

Usage: Run this query within any SQL interface in PostgreSQL.

Example: List All Tables in a Specific Schema Using psql

Code:

-- Use within the psql command-line tool
\dt public.*

Explanation:

• \dt: Lists all tables in the current database.
• public.*: Filters tables within the public schema. Replace public with any other schema name to filter accordingly.

Example: List Tables in a Schema Using information_schema.tables

Code:

-- Select tables specifically in the 'public' schema
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'public'
      AND table_type = 'BASE TABLE';

Explanation:

• information_schema.tables: A system view that contains information on all tables.
• table_schema = 'public': Filters tables to only those in the public schema.
• table_type = 'BASE TABLE': Ensures only base tables (and not views or temporary tables) are included.

Example: List All Tables in All Schemas

Code:

-- List all tables across all schemas in the database
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';

Explanation:

• Omitting table_schema in the WHERE clause will list tables in every schema within the database.
• This can help you get an overview of all tables, regardless of schema.

Full Example:

Code:

-- List all tables in the 'public' schema using psql meta-command
-- Use this within the psql command-line interface
\dt public.*

-- List all tables in the 'public' schema using an SQL query
SELECT table_schema, table_name
-- Get the schema and table name from information_schema.tables
FROM information_schema.tables
-- Filter by the 'public' schema
WHERE table_schema = 'public'
-- Only include base tables, not views or temporary tables
AND table_type = 'BASE TABLE';

-- List all tables in all schemas within the current database
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'; 

All PostgreSQL Questions, Answers, and Code Snippets Collection.