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.
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:
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:
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:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4