PostgreSQL List All Tables
Using the information_schema schema
- To list all table names, you can query the
tables
table within this schema: - The
information_schema
schema provides metadata about the database, including table names.
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'; -- Filter for tables in the public schema
- Replace
'public'
with the desired schema name if you want to list tables from a different schema.
Using the pg_catalog schema
- You can use the
pg_tables
table to list all table names: - The
pg_catalog
schema contains system catalogs that store information about the database.
SELECT relname AS table_name
FROM pg_catalog.pg_tables
WHERE schemaname = 'public'; -- Filter for tables in the public schema
- Similar to the
information_schema
method, replace'public'
with the desired schema name.
Additional filtering
- You can further filter the results based on specific criteria using
WHERE
clauses. For example, to list only tables that start with a certain prefix:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name LIKE 'my_table%';
- Use appropriate comparison operators (
=
,<>
,LIKE
, etc.) and conditions to filter the results based on your requirements.
Key points
- Choose the method that best suits your preferences and the specific requirements of your application.
- You can filter the results based on schema name and other criteria using
WHERE
clauses. - The
information_schema
andpg_catalog
schemas both provide methods to list table names.
Understanding the PostgreSQL Queries to List All Table Names
The information_schema
is a built-in database schema in PostgreSQL that provides metadata about the database. To list all table names, we can query the tables
table:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';
WHERE table_schema = 'public'
: This filters the results to only include tables in thepublic
schema. You can replace'public'
with another schema name if needed.information_schema.tables
: This specifies the table we want to query within theinformation_schema
schema.table_name
: This column returns the name of the table.
The pg_catalog
schema contains system catalogs that store information about the database. We can use the pg_tables
table to list table names:
SELECT relname AS table_name
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';
WHERE schemaname = 'public'
: This filters the results to only include tables in thepublic
schema.pg_catalog.pg_tables
: This specifies the table we want to query within thepg_catalog
schema.
Example Usage in a Programming Context
Here's a Python example using the psycopg2
library to execute the query and retrieve the table names:
import psycopg2
def list_tables(conn):
cursor = conn.cursor()
cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';")
tables = cursor.fetchall()
cursor.close()
return tables
if __name__ == "__main__":
conn = psycopg2.connect(
dbname="your_database_name",
user="your_username",
password="your_password",
host="your_host"
)
table_names = list_tables(conn)
for table_name in table_names:
print(table_name[0]) # Access the table name
conn.close()
This code:
- Connects to the PostgreSQL database.
- Executes the query using the
cursor.execute()
method. - Fetches the results using
cursor.fetchall()
. - Prints the table names.
- Closes the connection.
Alternative Methods for Listing Tables in PostgreSQL
While the information_schema
and pg_catalog
methods are commonly used, there are a few other approaches you can consider:
Using the pg_class System Catalog
The pg_class
system catalog provides detailed information about relations (tables, views, indexes, etc.) in the database. You can use it to list table names:
SELECT relname AS table_name
FROM pg_class
WHERE relkind = 'r'; -- Filter for relations of type 'r' (table)
The pg_namespace
catalog stores information about schemas. To list tables within a specific schema:
SELECT relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'; -- Filter for tables in the 'public' schema
Using the pg_tables View
The pg_tables
view is a pre-defined view that provides a simplified view of the pg_class
and pg_namespace
catalogs. You can use it for listing tables:
SELECT table_name
FROM pg_tables
WHERE schemaname = 'public';
Using the pg_catalog.pg_stat_all_tables View
This view provides statistics about all tables in the database, including the table name. You can use it to list tables:
SELECT relname AS table_name
FROM pg_catalog.pg_stat_all_tables;
Choosing the Right Method
The best method for your application depends on your specific needs and preferences. Here are some factors to consider:
- Additional information
If you need to retrieve additional information about the tables (e.g., schema, owner, size), thepg_class
orpg_catalog.pg_stat_all_tables
views might be more suitable. - Performance
If you need to query table names frequently, using thepg_class
orpg_catalog.pg_stat_all_tables
views might be more efficient. - Simplicity
Thepg_tables
view is often the simplest option.
postgresql postgresql-9.2 information-schema