「このデータベースにどんなテーブルがあるの？」 「インデックスやビューの一覧を確認したい…」 「誰がどのオブジェクトにアクセスできるか知りたい…」
PostgreSQLでデータベースを管理していると、オブジェクトの全体像を把握したい場面が必ずあります。
実は、PostgreSQLには様々な方法でオブジェクト一覧を取得する仕組みがあります。psqlのメタコマンド、information_schema、システムカタログ…それぞれに特徴があり、用途によって使い分けが重要！
この記事では、あらゆるオブジェクトの一覧取得方法から、実用的なスクリプトまで、すべてお伝えします。
psqlメタコマンド：最速でオブジェクト一覧を確認
🚀 主要なメタコマンド一覧
-- psqlコマンドラインで実行
\l -- データベース一覧
\dt -- テーブル一覧
\dv -- ビュー一覧
\di -- インデックス一覧
\ds -- シーケンス一覧
\df -- 関数一覧
\du -- ユーザー（ロール）一覧
\dn -- スキーマ一覧
\dx -- 拡張機能一覧
\dT -- データ型一覧
\z -- アクセス権限一覧
📊 詳細情報を含む拡張コマンド
-- より詳細な情報を表示（+を付ける）
\dt+ -- テーブル一覧（サイズ、説明付き）
\di+ -- インデックス一覧（サイズ付き）
\df+ -- 関数一覧（ソースコード付き）
\l+ -- データベース一覧（サイズ付き）
-- スキーマを指定
\dt public.* -- publicスキーマのテーブル
\dt myschema.* -- 特定スキーマのテーブル
\dt *.users -- 全スキーマのusersテーブル
-- パターンマッチング
\dt *user* -- userを含むテーブル名
\df *get* -- getを含む関数名
🎨 実用的な使用例
-- 大きなテーブルを探す
\dt+
-- 結果例：
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+---------------+-------+----------+-------------+--------+-------------
public | orders | table | postgres | permanent | 1234 MB| 注文データ
public | users | table | postgres | permanent | 456 MB | ユーザー
public | products | table | postgres | permanent | 89 MB | 商品マスタ
-- 特定スキーマの全オブジェクトを確認
\d myschema.*
information_schema：標準SQLでオブジェクト情報を取得
📚 主要なビュー一覧
-- テーブル一覧
SELECT
table_catalog AS database,
table_schema AS schema,
table_name,
table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;
-- カラム情報を含むテーブル一覧
SELECT
t.table_schema,
t.table_name,
COUNT(c.column_name) AS column_count,
STRING_AGG(c.column_name || ' ' || c.data_type, ', ' ORDER BY c.ordinal_position) AS columns
FROM information_schema.tables t
JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE t.table_schema = 'public'
GROUP BY t.table_schema, t.table_name
ORDER BY t.table_name;
🔍 ビュー一覧の取得
-- ビュー一覧（定義も含む）
SELECT
table_schema AS schema_name,
table_name AS view_name,
view_definition
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;
-- マテリアライズドビューは別途取得が必要
SELECT
schemaname,
matviewname AS view_name,
definition
FROM pg_matviews
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
🔑 制約とインデックス
-- 主キー・外部キー制約一覧
SELECT
tc.table_schema,
tc.table_name,
tc.constraint_name,
tc.constraint_type,
STRING_AGG(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) AS columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
WHERE tc.table_schema = 'public'
GROUP BY tc.table_schema, tc.table_name, tc.constraint_name, tc.constraint_type
ORDER BY tc.table_name, tc.constraint_type;
-- インデックス一覧（pg_indexesビュー使用）
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;
システムカタログ：最も詳細な情報を取得
🗂️ pg_classを使った全オブジェクト取得
-- 全オブジェクト種類を一覧表示
SELECT
n.nspname AS schema_name,
c.relname AS object_name,
CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'v' THEN 'VIEW'
WHEN 'm' THEN 'MATERIALIZED VIEW'
WHEN 'i' THEN 'INDEX'
WHEN 'S' THEN 'SEQUENCE'
WHEN 't' THEN 'TOAST TABLE'
WHEN 'f' THEN 'FOREIGN TABLE'
WHEN 'p' THEN 'PARTITIONED TABLE'
WHEN 'I' THEN 'PARTITIONED INDEX'
END AS object_type,
pg_get_userbyid(c.relowner) AS owner,
pg_size_pretty(pg_relation_size(c.oid)) AS size,
obj_description(c.oid, 'pg_class') AS description
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND c.relkind IN ('r', 'v', 'm', 'i', 'S', 'f', 'p')
ORDER BY n.nspname, object_type, c.relname;
📊 テーブル統計情報
-- テーブルの詳細統計
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS indexes_size,
n_live_tup AS row_count,
n_dead_tup AS dead_rows,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
関数・プロシージャ一覧の取得
🔧 ユーザー定義関数の一覧
-- 関数一覧（引数、戻り値型を含む）
SELECT
n.nspname AS schema_name,
p.proname AS function_name,
pg_get_function_identity_arguments(p.oid) AS arguments,
pg_get_function_result(p.oid) AS return_type,
l.lanname AS language,
CASE p.prokind
WHEN 'f' THEN 'FUNCTION'
WHEN 'p' THEN 'PROCEDURE'
WHEN 'a' THEN 'AGGREGATE'
WHEN 'w' THEN 'WINDOW'
END AS kind,
obj_description(p.oid, 'pg_proc') AS description
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;
-- 関数のソースコードを含む
SELECT
n.nspname || '.' || p.proname || '(' ||
pg_get_function_identity_arguments(p.oid) || ')' AS function_signature,
pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
ORDER BY p.proname;
権限とアクセス制御の一覧
🔐 オブジェクト別の権限一覧
-- テーブル権限の詳細
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
pg_get_userbyid(c.relowner) AS owner,
CASE
WHEN has_table_privilege(r.rolname, n.nspname||'.'||c.relname, 'SELECT') THEN 'YES'
ELSE 'NO'
END AS select_permission,
CASE
WHEN has_table_privilege(r.rolname, n.nspname||'.'||c.relname, 'INSERT') THEN 'YES'
ELSE 'NO'
END AS insert_permission,
CASE
WHEN has_table_privilege(r.rolname, n.nspname||'.'||c.relname, 'UPDATE') THEN 'YES'
ELSE 'NO'
END AS update_permission,
CASE
WHEN has_table_privilege(r.rolname, n.nspname||'.'||c.relname, 'DELETE') THEN 'YES'
ELSE 'NO'
END AS delete_permission
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN pg_roles r
WHERE c.relkind = 'r'
AND n.nspname = 'public'
AND r.rolname NOT LIKE 'pg_%'
ORDER BY n.nspname, c.relname, r.rolname;
-- より詳細な権限情報
WITH acl_info AS (
SELECT
n.nspname AS schema_name,
c.relname AS object_name,
c.relkind AS object_type,
unnest(c.relacl)::text AS acl_entry
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
)
SELECT
schema_name,
object_name,
CASE object_type
WHEN 'r' THEN 'TABLE'
WHEN 'v' THEN 'VIEW'
END AS type,
split_part(acl_entry, '=', 1) AS grantee,
split_part(split_part(acl_entry, '=', 2), '/', 1) AS privileges,
split_part(acl_entry, '/', 2) AS grantor
FROM acl_info
ORDER BY schema_name, object_name, grantee;
実用的なオブジェクト管理スクリプト
📋 データベース全体のサマリー
-- データベースオブジェクトのサマリー
SELECT
'TABLES' AS object_type,
COUNT(*) AS count,
pg_size_pretty(SUM(pg_total_relation_size(schemaname||'.'||tablename))) AS total_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
UNION ALL
SELECT
'INDEXES' AS object_type,
COUNT(*) AS count,
pg_size_pretty(SUM(pg_relation_size(schemaname||'.'||indexname))) AS total_size
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
UNION ALL
SELECT
'VIEWS' AS object_type,
COUNT(*) AS count,
NULL AS total_size
FROM pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
UNION ALL
SELECT
'FUNCTIONS' AS object_type,
COUNT(*) AS count,
NULL AS total_size
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY object_type;
🔍 未使用オブジェクトの検出
-- 長期間使用されていないテーブル
SELECT
schemaname,
tablename,
n_live_tup AS row_count,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
GREATEST(
COALESCE(last_vacuum, '1970-01-01'::date),
COALESCE(last_autovacuum, '1970-01-01'::date),
COALESCE(last_analyze, '1970-01-01'::date),
COALESCE(last_autoanalyze, '1970-01-01'::date)
) AS last_activity
FROM pg_stat_user_tables
WHERE GREATEST(
COALESCE(last_vacuum, '1970-01-01'::date),
COALESCE(last_autovacuum, '1970-01-01'::date),
COALESCE(last_analyze, '1970-01-01'::date),
COALESCE(last_autoanalyze, '1970-01-01'::date)
) < CURRENT_DATE - INTERVAL '30 days'
ORDER BY last_activity;
-- インデックスの使用状況
SELECT
schemaname,
tablename,
indexname,
idx_scan AS index_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched,
pg_size_pretty(pg_relation_size(schemaname||'.'||indexname)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- 一度も使用されていないインデックス
ORDER BY pg_relation_size(schemaname||'.'||indexname) DESC;
🛠️ オブジェクト依存関係の確認
-- オブジェクトの依存関係を表示
WITH RECURSIVE dep_tree AS (
-- 起点となるオブジェクト
SELECT
0 AS level,
c.oid AS object_id,
n.nspname || '.' || c.relname AS object_name,
NULL::oid AS depends_on_id,
NULL::text AS depends_on_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname = 'target_table'
UNION ALL
-- 依存するオブジェクト
SELECT
dt.level + 1,
c2.oid,
n2.nspname || '.' || c2.relname,
dt.object_id,
dt.object_name
FROM dep_tree dt
JOIN pg_depend d ON d.refobjid = dt.object_id
JOIN pg_class c2 ON c2.oid = d.objid
JOIN pg_namespace n2 ON n2.oid = c2.relnamespace
WHERE dt.level < 3 -- 階層の深さを制限
AND d.deptype IN ('n', 'a') -- normal, automatic依存
)
SELECT
level,
object_name,
depends_on_name
FROM dep_tree
ORDER BY level, object_name;
JSON形式でのエクスポート
📄 オブジェクト情報をJSON形式で出力
-- データベーススキーマをJSON形式で取得
SELECT json_build_object(
'database', current_database(),
'timestamp', NOW(),
'schemas', (
SELECT json_agg(
json_build_object(
'schema_name', n.nspname,
'tables', (
SELECT json_agg(
json_build_object(
'table_name', c.relname,
'columns', (
SELECT json_agg(
json_build_object(
'column_name', a.attname,
'data_type', pg_catalog.format_type(a.atttypid, a.atttypmod),
'is_nullable', NOT a.attnotnull
) ORDER BY a.attnum
)
FROM pg_attribute a
WHERE a.attrelid = c.oid
AND a.attnum > 0
AND NOT a.attisdropped
)
)
)
FROM pg_class c
WHERE c.relnamespace = n.oid
AND c.relkind = 'r'
)
)
)
FROM pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
)
) AS database_schema;
便利な関数とビューの作成
🎯 再利用可能なビュー
-- オブジェクト一覧ビューの作成
CREATE OR REPLACE VIEW v_database_objects AS
SELECT
n.nspname AS schema_name,
c.relname AS object_name,
CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'v' THEN 'VIEW'
WHEN 'm' THEN 'MATERIALIZED VIEW'
WHEN 'i' THEN 'INDEX'
WHEN 'S' THEN 'SEQUENCE'
END AS object_type,
pg_get_userbyid(c.relowner) AS owner,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
c.reltuples::BIGINT AS estimated_rows,
obj_description(c.oid, 'pg_class') AS description
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND c.relkind IN ('r', 'v', 'm', 'i', 'S');
-- 使用例
SELECT * FROM v_database_objects
WHERE object_type = 'TABLE'
ORDER BY size DESC;
📊 スキーマ比較関数
-- 2つのスキーマを比較する関数
CREATE OR REPLACE FUNCTION compare_schemas(
schema1 TEXT,
schema2 TEXT
) RETURNS TABLE(
object_name TEXT,
in_schema1 BOOLEAN,
in_schema2 BOOLEAN,
difference TEXT
) AS $$
BEGIN
RETURN QUERY
WITH schema1_objects AS (
SELECT c.relname, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = schema1
),
schema2_objects AS (
SELECT c.relname, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = schema2
)
SELECT
COALESCE(s1.relname, s2.relname) AS object_name,
s1.relname IS NOT NULL AS in_schema1,
s2.relname IS NOT NULL AS in_schema2,
CASE
WHEN s1.relname IS NULL THEN 'Only in ' || schema2
WHEN s2.relname IS NULL THEN 'Only in ' || schema1
WHEN s1.relkind != s2.relkind THEN 'Different types'
ELSE 'Same'
END AS difference
FROM schema1_objects s1
FULL OUTER JOIN schema2_objects s2 ON s1.relname = s2.relname
WHERE s1.relname IS NULL OR s2.relname IS NULL OR s1.relkind != s2.relkind;
END;
$$ LANGUAGE plpgsql;
まとめ：適切な方法でオブジェクト管理を効率化！
PostgreSQLのオブジェクト一覧取得には、用途に応じた最適な方法があります。
使い分けの指針：
- 素早く確認したい → psqlメタコマンド（\dt, \di等）
- 標準SQLで取得したい → information_schema
- 詳細情報が必要 → システムカタログ（pg_class等）
- 統計情報も含めたい → pg_stat_*ビュー
- プログラムから利用 → information_schemaまたはJSON出力
便利なコマンドTOP5：
\dt+ -- テーブル一覧（サイズ付き）
\di+ -- インデックス一覧
\df -- 関数一覧
\z -- 権限一覧
SELECT * FROM pg_stat_user_tables; -- 統計情報付きテーブル一覧
この記事のクエリを活用して、データベースの全体像を把握し、効率的に管理しましょう！
PostgreSQLオブジェクト管理をマスターして、DBAレベルアップ！ 🎯🐘
コメント