A
PostgreSQL's information_schema.tables view requires filtering by the table_schema column to query tables within a specific schema. The information schema is SQL standard-compliant and portable across databases, unlike PostgreSQL-specific system catalogs.
Query Pattern:
-- Query tables in a specific schema
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'my_schema'
AND table_type = 'BASE TABLE'; -- Excludes views
-- Example: List all user tables (exclude system schemas)
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;
-- Check if specific table exists in schema
SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'users'
) AS table_exists;
Key Columns:
| Column | Description | Values |
|---|---|---|
table_catalog |
Database name | Current database |
table_schema |
Schema name | public, my_schema, etc. |
table_name |
Table name | users, orders, etc. |
table_type |
Object type | BASE TABLE, VIEW, FOREIGN |
Using asyncpg:
import asyncpg
async def check_table_exists(schema: str, table: str) -> bool:
"""
Check if table exists in specified schema.
"""
conn = await asyncpg.connect(
host='localhost',
port=5432,
user='postgres',
password='password',
database='mydb'
)
try:
exists = await conn.fetchval(
"""
SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = $1
AND table_name = $2
AND table_type = 'BASE TABLE'
)
""",
schema,
table
)
return exists
finally:
await conn.close()
# Usage
exists = await check_table_exists('public', 'users')
print(f"Table exists: {exists}")
Common Patterns:
-- 1. List all tables in database (excluding system)
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_type = 'BASE TABLE';
-- 2. List tables with column count
SELECT
t.table_schema,
t.table_name,
COUNT(c.column_name) AS column_count
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE t.table_schema = 'public'
AND t.table_type = 'BASE TABLE'
GROUP BY t.table_schema, t.table_name;
-- 3. Search for tables by name pattern
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE '%user%'
AND table_type = 'BASE TABLE';
Best Practices:
- Always filter by
table_schema- Improves performance and accuracy - Use
table_type = 'BASE TABLE'- Excludes views unless specifically needed - Avoid
SELECT *- Select only needed columns for performance - Use EXISTS for existence checks - More efficient than COUNT(*)
- Exclude system schemas -
pg_catalog,information_schemafor user tables
information_schema vs pg_catalog:
-- information_schema: Standard, portable across databases
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';
-- pg_catalog: PostgreSQL-specific, more detailed info
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public';
-- pg_catalog also provides size info (not in information_schema)
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public';
Performance Considerations:
According to PostgreSQL documentation and best practices (2024), querying information_schema can impact performance on large databases. Apply filters and select only required columns to reduce computational complexity. For PostgreSQL-specific features like table sizes and row counts, use pg_catalog views instead.
Complete FastAPI Example:
from fastapi import FastAPI, HTTPException
import asyncpg
from typing import List, Dict
app = FastAPI()
DATABASE_URL = "postgresql://user:pass@localhost/mydb"
@app.get("/tables/{schema}")
async def list_tables(schema: str) -> List[Dict[str, str]]:
"""
List all tables in specified schema.
"""
conn = await asyncpg.connect(DATABASE_URL)
try:
tables = await conn.fetch(
"""
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema = $1
AND table_type = 'BASE TABLE'
ORDER BY table_name
""",
schema
)
return [
{
"schema": row['table_schema'],
"name": row['table_name'],
"type": row['table_type']
}
for row in tables
]
finally:
await conn.close()
@app.get("/tables/{schema}/{table}/exists")
async def table_exists(schema: str, table: str) -> Dict[str, bool]:
"""
Check if table exists in schema.
"""
conn = await asyncpg.connect(DATABASE_URL)
try:
exists = await conn.fetchval(
"""
SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = $1
AND table_name = $2
)
""",
schema,
table
)
return {"exists": exists}
finally:
await conn.close()
Security Note:
The information_schema is read-only and respects user permissions. Users can only see tables they have privileges to access.
Version Note: information_schema behavior consistent since PostgreSQL 9.1+, recommended for PostgreSQL 12+
99% confidence