「このテーブルにどんなカラムがあるか確認したい」 「カラムのデータ型や制約を一覧で見たい」 「複数テーブルのカラム情報をまとめて取得したい」
PostgreSQLでデータベース開発をしていると、こんなニーズが頻繁に発生しませんか？
カラム一覧の効率的な確認方法を知ることは、開発速度を大幅に向上させます。適切な方法を使えば、テーブル構造の理解、ドキュメント作成、データ移行の計画などが格段に楽になります。
この記事では、PostgreSQLでカラム一覧を取得するすべての方法から、実践的な活用例、自動化スクリプトまで、実例を交えながら徹底的に解説していきます。
🔍 基本的なカラム一覧の確認方法
psqlメタコマンドでの確認
\d コマンド（最も簡単な方法）
基本的な使い方：
-- テーブルの詳細情報（カラム一覧含む）
\d users
-- 実行結果
Table "public.users"
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+-----------------------------------
user_id | integer | | not null | nextval('users_user_id_seq')
username | character varying(50) | | not null |
email | character varying(100) | | not null |
created_at | timestamp without time zone | | | CURRENT_TIMESTAMP
updated_at | timestamp without time zone | | |
is_active | boolean | | | true
age | integer | | |
bio | text | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_email_key" UNIQUE, btree (email)
\d+ コマンド（詳細情報付き）
-- より詳細な情報を表示
\d+ orders
-- 実行結果（追加情報含む）
Table "public.orders"
Column | Type | ... | Storage | Stats target | Description
--------------+-----------------------------+-----+----------+--------------+-------------
order_id | integer | ... | plain | | 注文ID
customer_id | integer | ... | plain | | 顧客ID
order_date | date | ... | plain | | 注文日
total_amount | numeric(10,2) | ... | main | | 合計金額
status | character varying(20) | ... | extended | | 注文ステータス
information_schema を使用した確認
標準SQLでのカラム情報取得
基本的なクエリ：
-- 特定テーブルのカラム一覧
SELECT
column_name,
data_type,
character_maximum_length,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'users'
ORDER BY ordinal_position;
-- 実行結果
column_name | data_type | character_maximum_length | is_nullable | column_default
------------+-------------------+--------------------------+-------------+-----------------------------
user_id | integer | | NO | nextval('users_user_id_seq')
username | character varying | 50 | NO |
email | character varying | 100 | NO |
created_at | timestamp | | YES | CURRENT_TIMESTAMP
updated_at | timestamp | | YES |
is_active | boolean | | YES | true
より詳細な情報を含むクエリ
-- 完全な型情報と制約を含む
SELECT
c.column_name,
c.data_type,
CASE
WHEN c.character_maximum_length IS NOT NULL
THEN c.data_type || '(' || c.character_maximum_length || ')'
WHEN c.numeric_precision IS NOT NULL
THEN c.data_type || '(' || c.numeric_precision || ',' || c.numeric_scale || ')'
ELSE c.data_type
END as full_data_type,
c.is_nullable,
c.column_default,
c.is_identity,
c.is_generated,
c.generation_expression
FROM information_schema.columns c
WHERE c.table_schema = 'public'
AND c.table_name = 'users'
ORDER BY c.ordinal_position;
システムカタログでの確認
pg_catalog の活用
-- システムカタログを直接参照
SELECT
a.attname as column_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
a.attnotnull as not_null,
a.atthasdef as has_default,
pg_get_expr(d.adbin, d.adrelid) as default_value,
col_description(a.attrelid, a.attnum) as comment
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
WHERE a.attrelid = 'public.users'::regclass
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum;
-- 実行結果
column_name | data_type | not_null | has_default | default_value | comment
------------+-----------------------------+----------+-------------+--------------------------------+---------
user_id | integer | t | t | nextval('users_user_id_seq') | ユーザーID
username | character varying(50) | t | f | | ユーザー名
email | character varying(100) | t | f | | メールアドレス
created_at | timestamp without time zone | f | t | CURRENT_TIMESTAMP | 作成日時
📊 複数テーブルのカラム一覧取得
データベース全体のカラム情報
すべてのテーブルとカラムを一覧化
-- データベース内の全テーブルのカラム一覧
SELECT
t.table_schema,
t.table_name,
c.column_name,
c.data_type,
c.is_nullable,
c.column_default
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 NOT IN ('pg_catalog', 'information_schema')
AND t.table_type = 'BASE TABLE'
ORDER BY t.table_schema, t.table_name, c.ordinal_position;
特定パターンのテーブル
LIKE条件での絞り込み
-- 特定のプレフィックスを持つテーブルのカラム一覧
WITH target_tables AS (
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name LIKE 'user_%'
AND table_type = 'BASE TABLE'
)
SELECT
t.table_name,
c.column_name,
c.data_type,
CASE
WHEN c.is_nullable = 'NO' THEN 'NOT NULL'
ELSE ''
END as nullable,
COALESCE(c.column_default, '') as default_value
FROM target_tables t
JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
ORDER BY t.table_name, c.ordinal_position;
🔧 カラムの詳細情報取得
制約情報の確認
PRIMARY KEY, FOREIGN KEY, UNIQUE制約
-- カラムに設定された制約を確認
WITH column_constraints AS (
SELECT
kcu.table_schema,
kcu.table_name,
kcu.column_name,
tc.constraint_type,
tc.constraint_name
FROM information_schema.key_column_usage kcu
JOIN information_schema.table_constraints tc
ON kcu.constraint_name = tc.constraint_name
AND kcu.table_schema = tc.table_schema
WHERE kcu.table_schema = 'public'
)
SELECT
c.table_name,
c.column_name,
c.data_type,
STRING_AGG(
cc.constraint_type,
', '
ORDER BY
CASE cc.constraint_type
WHEN 'PRIMARY KEY' THEN 1
WHEN 'FOREIGN KEY' THEN 2
WHEN 'UNIQUE' THEN 3
ELSE 4
END
) as constraints
FROM information_schema.columns c
LEFT JOIN column_constraints cc
ON c.table_schema = cc.table_schema
AND c.table_name = cc.table_name
AND c.column_name = cc.column_name
WHERE c.table_schema = 'public'
AND c.table_name IN ('users', 'orders', 'order_items')
GROUP BY c.table_name, c.column_name, c.data_type, c.ordinal_position
ORDER BY c.table_name, c.ordinal_position;
CHECK制約の確認
-- CHECK制約を含むカラム情報
SELECT
c.table_name,
c.column_name,
c.data_type,
cc.check_clause
FROM information_schema.columns c
LEFT JOIN information_schema.check_constraints cc
ON c.table_schema = cc.constraint_schema
AND EXISTS (
SELECT 1
FROM information_schema.constraint_column_usage ccu
WHERE ccu.constraint_name = cc.constraint_name
AND ccu.table_name = c.table_name
AND ccu.column_name = c.column_name
)
WHERE c.table_schema = 'public'
AND cc.check_clause IS NOT NULL
ORDER BY c.table_name, c.ordinal_position;
インデックス情報の確認
-- カラムに設定されたインデックス
SELECT
t.tablename,
i.indexname,
i.indexdef,
ARRAY_AGG(a.attname ORDER BY array_position(ix.indkey, a.attnum)) as columns
FROM pg_indexes i
JOIN pg_class c ON c.relname = i.indexname
JOIN pg_index ix ON ix.indexrelid = c.oid
JOIN pg_class t2 ON t2.oid = ix.indrelid
JOIN pg_attribute a ON a.attrelid = t2.oid AND a.attnum = ANY(ix.indkey)
JOIN pg_tables t ON t.tablename = t2.relname
WHERE t.schemaname = 'public'
GROUP BY t.tablename, i.indexname, i.indexdef
ORDER BY t.tablename, i.indexname;
💡 実践的な活用例
テーブル定義書の自動生成
Markdown形式での出力
-- Markdownテーブルとして出力
CREATE OR REPLACE FUNCTION generate_table_documentation(p_table_name TEXT)
RETURNS TABLE(documentation TEXT) AS $$
BEGIN
-- ヘッダー
RETURN QUERY SELECT '## Table: ' || p_table_name;
RETURN QUERY SELECT '';
RETURN QUERY SELECT '| Column | Type | Nullable | Default | Description |';
RETURN QUERY SELECT '|--------|------|----------|---------|-------------|';
-- カラム情報
RETURN QUERY
SELECT
'| ' ||
a.attname || ' | ' ||
pg_catalog.format_type(a.atttypid, a.atttypmod) || ' | ' ||
CASE WHEN a.attnotnull THEN 'NOT NULL' ELSE 'YES' END || ' | ' ||
COALESCE(pg_get_expr(d.adbin, d.adrelid), '-') || ' | ' ||
COALESCE(col_description(a.attrelid, a.attnum), '-') || ' |'
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
WHERE a.attrelid = p_table_name::regclass
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum;
-- インデックス情報
RETURN QUERY SELECT '';
RETURN QUERY SELECT '### Indexes';
RETURN QUERY
SELECT '- ' || indexname || ': ' || indexdef
FROM pg_indexes
WHERE tablename = p_table_name
AND schemaname = 'public';
END;
$$ LANGUAGE plpgsql;
-- 使用例
SELECT * FROM generate_table_documentation('users');
データ型の統計
プロジェクト全体のデータ型使用状況
-- データ型の使用頻度を分析
WITH data_type_stats AS (
SELECT
data_type,
COUNT(*) as usage_count,
COUNT(DISTINCT table_name) as table_count,
STRING_AGG(DISTINCT table_name, ', ' ORDER BY table_name) as used_in_tables
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY data_type
)
SELECT
data_type,
usage_count,
table_count,
ROUND(usage_count::numeric / SUM(usage_count) OVER() * 100, 2) as percentage,
CASE
WHEN LENGTH(used_in_tables) > 50
THEN SUBSTRING(used_in_tables, 1, 47) || '...'
ELSE used_in_tables
END as sample_tables
FROM data_type_stats
ORDER BY usage_count DESC;
カラム名の命名規則チェック
-- 命名規則に違反するカラムを検出
WITH naming_check AS (
SELECT
table_name,
column_name,
CASE
WHEN column_name !~ '^[a-z][a-z0-9_]*$' THEN 'Invalid format'
WHEN column_name ~ '^[0-9]' THEN 'Starts with number'
WHEN column_name ~ '[A-Z]' THEN 'Contains uppercase'
WHEN column_name ~ '__' THEN 'Double underscore'
WHEN LENGTH(column_name) > 30 THEN 'Too long (>30 chars)'
ELSE 'OK'
END as issue
FROM information_schema.columns
WHERE table_schema = 'public'
)
SELECT
table_name,
column_name,
issue,
'ALTER TABLE ' || table_name || ' RENAME COLUMN ' ||
column_name || ' TO ' || LOWER(REPLACE(column_name, '__', '_')) || ';' as fix_sql
FROM naming_check
WHERE issue != 'OK'
ORDER BY table_name, column_name;
🚀 高度な分析クエリ
カラムの重複検出
同じ名前・型のカラムを持つテーブル
-- 同じカラム名と型を持つテーブルを検出（正規化の候補）
WITH column_patterns AS (
SELECT
column_name,
data_type,
COUNT(DISTINCT table_name) as table_count,
STRING_AGG(table_name, ', ' ORDER BY table_name) as tables
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_name NOT IN ('id', 'created_at', 'updated_at') -- 共通カラムを除外
GROUP BY column_name, data_type
HAVING COUNT(DISTINCT table_name) > 1
)
SELECT
column_name,
data_type,
table_count,
tables,
'Candidate for normalization' as recommendation
FROM column_patterns
ORDER BY table_count DESC, column_name;
NULL値の多いカラム検出
-- NULL値が多いカラムを検出（設計見直しの候補）
CREATE OR REPLACE FUNCTION analyze_null_columns()
RETURNS TABLE(
table_name TEXT,
column_name TEXT,
total_rows BIGINT,
null_count BIGINT,
null_percentage NUMERIC
) AS $$
DECLARE
v_record RECORD;
v_query TEXT;
v_total BIGINT;
v_nulls BIGINT;
BEGIN
FOR v_record IN
SELECT
c.table_name AS tbl,
c.column_name AS col
FROM information_schema.columns c
WHERE c.table_schema = 'public'
AND c.is_nullable = 'YES'
LOOP
v_query := format(
'SELECT COUNT(*), COUNT(*) - COUNT(%I) FROM %I',
v_record.col, v_record.tbl
);
EXECUTE v_query INTO v_total, v_nulls;
IF v_total > 0 THEN
RETURN QUERY SELECT
v_record.tbl::TEXT,
v_record.col::TEXT,
v_total,
v_nulls,
ROUND((v_nulls::NUMERIC / v_total) * 100, 2);
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- 使用例（NULL率が50%以上のカラムを表示）
SELECT * FROM analyze_null_columns()
WHERE null_percentage >= 50
ORDER BY null_percentage DESC;
カラムサイズの分析
-- 各カラムの実際のデータサイズを分析
CREATE OR REPLACE FUNCTION analyze_column_sizes(p_table_name TEXT)
RETURNS TABLE(
column_name TEXT,
data_type TEXT,
avg_size NUMERIC,
max_size NUMERIC,
total_size TEXT
) AS $$
DECLARE
v_column RECORD;
v_query TEXT;
BEGIN
FOR v_column IN
SELECT
a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod) as dtype
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = p_table_name::regclass
AND a.attnum > 0
AND NOT a.attisdropped
LOOP
v_query := format(
'SELECT
%L::TEXT,
%L::TEXT,
ROUND(AVG(pg_column_size(%I))::NUMERIC, 2),
MAX(pg_column_size(%I))::NUMERIC,
pg_size_pretty(SUM(pg_column_size(%I))::BIGINT)
FROM %I',
v_column.attname,
v_column.dtype,
v_column.attname,
v_column.attname,
v_column.attname,
p_table_name
);
RETURN QUERY EXECUTE v_query;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- 使用例
SELECT * FROM analyze_column_sizes('users');
📝 カラム変更履歴の追跡
DDLログの活用
-- カラム変更を追跡するトリガー
CREATE TABLE column_change_log (
id SERIAL PRIMARY KEY,
table_name TEXT,
column_name TEXT,
change_type TEXT,
old_definition TEXT,
new_definition TEXT,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
changed_by TEXT DEFAULT CURRENT_USER
);
-- イベントトリガーで変更を記録
CREATE OR REPLACE FUNCTION log_column_changes()
RETURNS event_trigger AS $$
DECLARE
v_obj RECORD;
BEGIN
FOR v_obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
INSERT INTO column_change_log (table_name, change_type, new_definition)
VALUES (
v_obj.object_identity,
v_obj.command_tag,
v_obj.object_identity
);
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER track_column_changes
ON ddl_command_end
WHEN TAG IN ('ALTER TABLE', 'CREATE TABLE', 'DROP TABLE')
EXECUTE FUNCTION log_column_changes();
🔄 他システムとの連携
JSON形式でのエクスポート
-- テーブル構造をJSON形式で出力
SELECT jsonb_pretty(
jsonb_build_object(
'table_name', table_name,
'columns', jsonb_agg(
jsonb_build_object(
'name', column_name,
'type', data_type,
'nullable', is_nullable,
'default', column_default,
'position', ordinal_position
) ORDER BY ordinal_position
)
)
) as table_structure
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'users'
GROUP BY table_name;
CSV形式でのエクスポート
-- カラム情報をCSVファイルに出力
COPY (
SELECT
table_name,
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position
) TO '/tmp/column_list.csv' WITH CSV HEADER;
🛠️ 便利な関数とビュー
カスタムビューの作成
-- よく使うカラム情報をビューとして定義
CREATE OR REPLACE VIEW v_column_details AS
SELECT
c.table_name,
c.column_name,
c.data_type,
CASE
WHEN c.character_maximum_length IS NOT NULL
THEN c.data_type || '(' || c.character_maximum_length || ')'
WHEN c.numeric_precision IS NOT NULL
THEN c.data_type || '(' || c.numeric_precision || ',' || c.numeric_scale || ')'
ELSE c.data_type
END as full_type,
c.is_nullable,
c.column_default,
pgd.description as comment,
COALESCE(
(SELECT STRING_AGG(tc.constraint_type, ', ')
FROM information_schema.key_column_usage kcu
JOIN information_schema.table_constraints tc
ON kcu.constraint_name = tc.constraint_name
WHERE kcu.table_name = c.table_name
AND kcu.column_name = c.column_name),
''
) as constraints
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_description pgd
ON pgd.objoid = (c.table_schema || '.' || c.table_name)::regclass
AND pgd.objsubid = c.ordinal_position
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position;
-- 使用例
SELECT * FROM v_column_details WHERE table_name = 'users';
検索機能付きカラム検索
-- カラム名で検索する関数
CREATE OR REPLACE FUNCTION search_columns(p_search_term TEXT)
RETURNS TABLE(
table_name TEXT,
column_name TEXT,
data_type TEXT,
column_comment TEXT
) AS $$
BEGIN
RETURN QUERY
SELECT
c.table_name::TEXT,
c.column_name::TEXT,
c.data_type::TEXT,
COALESCE(pgd.description, '')::TEXT
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_description pgd
ON pgd.objoid = (c.table_schema || '.' || c.table_name)::regclass
AND pgd.objsubid = c.ordinal_position
WHERE c.table_schema = 'public'
AND (
c.column_name ILIKE '%' || p_search_term || '%'
OR COALESCE(pgd.description, '') ILIKE '%' || p_search_term || '%'
)
ORDER BY
CASE WHEN c.column_name = p_search_term THEN 1
WHEN c.column_name ILIKE p_search_term || '%' THEN 2
ELSE 3
END,
c.table_name, c.column_name;
END;
$$ LANGUAGE plpgsql;
-- 使用例
SELECT * FROM search_columns('email');
💡 ベストプラクティス
カラム情報管理のコツ
-- 1. コメントを必ず付ける
COMMENT ON COLUMN users.email IS 'ユーザーのメールアドレス（ユニーク）';
-- 2. 定期的な構造チェック
CREATE OR REPLACE FUNCTION check_table_health()
RETURNS TABLE(
issue_type TEXT,
table_name TEXT,
details TEXT
) AS $$
BEGIN
-- コメントなしカラム
RETURN QUERY
SELECT
'Missing comment'::TEXT,
c.table_name::TEXT,
'Column: ' || c.column_name
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_description pgd
ON pgd.objoid = (c.table_schema || '.' || c.table_name)::regclass
AND pgd.objsubid = c.ordinal_position
WHERE c.table_schema = 'public'
AND pgd.description IS NULL;
-- インデックスなし外部キー
RETURN QUERY
SELECT
'Missing index on FK'::TEXT,
tc.table_name::TEXT,
'Column: ' || kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
SELECT 1 FROM pg_indexes i
WHERE i.tablename = tc.table_name
AND i.indexdef LIKE '%' || kcu.column_name || '%'
);
END;
$$ LANGUAGE plpgsql;
📚 まとめ：カラム一覧取得を極める
PostgreSQLでカラム一覧を効率的に取得する方法：
✅ psqlの\dコマンドで素早く確認
✅ information_schemaで標準SQL互換の取得
✅ pg_catalogで詳細な情報を取得
✅ カスタム関数で自動ドキュメント生成
✅ 定期的な構造チェックで品質維持
これらのテクニックを活用することで、データベース構造の把握と管理が格段に効率化されます。
今すぐ実践すべき3つのアクション：
- よく使うカラム確認クエリをビューとして保存
- テーブルとカラムにコメントを追加
- 自動ドキュメント生成スクリプトを作成
データベース構造を完全に把握して、効率的な開発を実現しましょう！
コメント