データベースやテーブル情報操作のまとめ【PostgreSQL】

2021年8月22日未分類

【頻出】データベースやテーブル情報操作のまとめ

データベースやテーブル情報操作のうち、特に頻出のものをまとめました。

 

1.テーブル一覧の取得

--データベースに存在するテーブルの取得(そのまま実行してみてください)
select tablename from pg_tables
where schemaname != 'pg_catalog' order by tablename;

ここで詳しく説明しています → テーブル一覧とデータ件数を取得するSQL

 

 

2.テーブルの列一覧の取得

--'テーブル名'に存在する列一覧を取得
select * from information_schema.columns
where table_name='テーブル名' order by ordinal_position;

ここで詳しく説明しています → テーブルの列一覧を取得する

 

 

3.ビュー一覧の取得

--ビュー一覧の取得(そのまま実行してみてください)
select * From pg_views
where schemaname = 'public';

ここで詳しく説明しています → データベース情報を取得する(ビュー、トリガー、インデックス)

 

 

4.データベース一覧の取得

--pgAdminでそのまま実行してみてください。2カラム目はDBのエンコーディングを表示
select datname, pg_encoding_to_char(encoding) from pg_database;

ここで詳しく説明しています → データベース情報を取得する(ビュー、トリガー、インデックス)

 

 

5.シーケンス一覧の取得

--シーケンス一覧の取得(そのまま実行してみてください)
SELECT relname FROM pg_class 
JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) 
WHERE relkind = 'S';

 

 

6.シーケンスの初期化

select SETVAL('seq_name', 1 , false); --シーケンス「seq_name」を1に設定

ここで詳しく説明しています → シーケンス(serial)を作成する

 

 

7.トリガー一覧の取得

--トリガー一覧の取得
SELECT * FROM information_schema.triggers;

 

 

8.テーブルデータ数の一覧を表示する

--テーブルごとのデータ数を表示する(そのまま実行してみてください)
SELECT pg_class.relname , pg_class.reltuples
FROM pg_stat_user_tables
INNER JOIN pg_class ON pg_stat_user_tables.relname = pg_class.relname
order by cast(pg_class.reltuples as numeric) desc;

ここで詳しく説明しています → テーブル一覧とデータ件数を取得するSQL

 

 

9.インデックス情報の取得

--idx_scan列は、インデックススキャンを使用した回数
select * From pg_stat_user_indexes; 

 

 

10.ディスク容量を調べる

-- 1.データベースの物理容量を取得する
SELECT datname, pg_size_pretty(pg_database_size(datname)) 
FROM pg_database; 
 
-- 2.テーブルの物理容量を取得する
SELECT relname, reltuples as rows, (relpages * 8192) as bytes
,((relpages * 8192) / (1024 * 1024)) AS mbytes 
FROM pg_class order by mbytes desc;

ここで詳しく説明しています → テーブルサイズを確認するSQL