接続中のセッションを確認する

次のSELECT文を実行すると、現在接続中のセッションを取得できます。

select * from pg_stat_activity 
where datname = 'データベース名';

 

 

接続中のセッションを切断する

接続中のセッションを切断するSQLです。

セッションの切断ができると、実行結果にtrueが返ります

-- 設定するpidはこの上の『接続中のセッションを確認する』で取得する
SELECT pg_terminate_backend(pidを設定); 

ANALYZE(アナライズ)とは

ANALYZEは、データベース内のテーブルの内容に関する統計情報を更新するコマンドです

 

発行されたクエリは、プランナ(オプティマイザ)が統計情報を参照して実行計画を作成します。

この統計情報はプランナが最適な実行計画を決定するために活用されているものです。

※統計情報といっても、テーブルのアクセス頻度のようなDBの統計情報ではない点に注意して下さい。

 

そのため、統計情報が最新化されていない場合、実行計画は最適なものを作成できない場合があり、

夜間バッチなどで大量のデータ更新などが行われた時、手動でANALYZEすることをお勧めします。

尚、AUTOVACUUMの実行時には、ANALYZEも同時に実行されます。

 

 

 

ANALYZEコマンド

ANALYZEコマンドを紹介します。(pgAdminでも実行できます)

-- ANALYZEコマンド
ANALYZE;

-- テーブル指定のANALYZE
ANALYZE テーブル名;

 

 

関連記事

https://postgresweb.com/post-5194

https://postgresweb.com/autovacuum

https://postgresweb.com/post-4047

 

 

 

VACUUM(バキューム)とは

PostgreSQLはデータを消しても実際には消えてはなく、削除フラグがついていて見えなくなっているだけの状態になっています。この削除データは定期的にきれいにする必要があり、この処理をVACUUMといいます。

昔のバージョンでは、このVACUUM処理は手動で行う必要がありましたが、現在、このVACUUM処理を自動で行う機能(AUTOVACUUM)がデフォルトでONになっています。

 

 

VACUUMとVACUUM FULL

VACUUMには通常のVACUUMと、VACUUM FULLの2つがあります。

(加えてVACUUM ANALYZEがありますがそれは後述します)

 

通常のVACUUMは、きれいにした領域をOSに返さない(=サイズは変わらない)かわりに処理は早く、VACUUM FULLでは、きれいにした領域をOSに返しますが処理は遅くなっています

 

また通常のVACUUMの場合、排他的ロックを取得しないため、テーブルへの通常の読み書き操作と並行して実行することができます。一方VACUUM FULLでは、排他的ロックが必要になります。

 

 

VACUUM

VACUUMをするコマンドは次のとおりです。 ※pgAdminのクエリツールからも実行できます。

-- テーブル名を指定してVACUUMする
VACUUM テーブル名;

-- VERBOSEオプションをつけると、実行時間等の詳細情報が表示されます
VACUUM VERBOSE テーブル名;

-- テーブル名を省略すると、すべてのテーブルを対象とします
VACUUM;

 

 

VACUUM FULL

VACUUM FULLのコマンドは次のとおりです。

-- 通常のVACUUM ※テーブル名を省略すると、すべてのテーブルを対象とします
VACUUM テーブル名;

-- FULLオプションあり
VACUUM FULL テーブル名;

 

 

VACUUM ANALYZE

VACUUM ANALYZEは、VACUUM後にANALYZEを実行します。

ANALYZEとは、データベース内のテーブルの内容に関する統計情報を更新するコマンドです。

-- VACUUM+ANALYZEをする ※テーブル名を省略すると、すべてのテーブルを対象とします
VACUUM ANALYZE テーブル名;

-- すべてのテーブル指定では、時間がかかるので注意
VACUUM ANALYZE;

 

あわせて読みたい

※ ANALYZEについてはこちらの記事で説明しています

https://postgresweb.com/post-5390

 

 

 

テーブルの最終VACUUM時間を確認する

次のselect文でvacuumの実行時間を確認できます。

select relname 
, last_vacuum , last_autovacuum
from pg_stat_all_tables order by relname;

データベース一覧の取得(SQL)

データベース一覧を取得するSQLを紹介します。

select 
    datname, 
    pg_encoding_to_char(encoding)  -- DBのエンコーディング
from pg_database;

テーブル情報を取得するにはここを参照してください。

 

 

データベース一覧の取得(コマンド)

データベース一覧を取得するには、次のコマンドを実行します。

※psqlでログイン後に「-l」または「--list」のみを実行すれば一覧が表示されます。

-l
--list

 

 

ビュー一覧の取得

データベースのビューを取得するSQLです。

select * From pg_views
where schemaname = 'public';

 

 

シーケンス一覧の取得

データベースのシーケンスを取得するSQLです。

SELECT relname FROM pg_class 
JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) 
WHERE relkind = 'S';

 

シーケンスを初期化するには次の文を使います。

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

 

 

トリガー一覧の取得

データベースのトリガーを取得するSQLです。

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

 

 

インデックス情報の取得

データベースのインデックスを取得するSQLです。

-- 実行後に表示されるidx_scan列は、インデックススキャンを使用した回数です
select * From pg_stat_user_indexes; 

データベースとテーブルの物理容量を取得する

データベースとテーブルの物理容量を取得するSQLです。

-- 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    -- サイズ(MB単位)
FROM pg_class order by mbytes desc;

テーブル一覧を取得する(SQL)

テーブル一覧を取得するSQL文を紹介します。

※ここで紹介するSQLは、そのまま実行できるので試してみて下さい。

--テーブル一覧をテーブル名順に取得するSQL
select tablename from pg_tables
where schemaname not in('pg_catalog','information_schema')
order by tablename;

 

 

テーブル一覧を取得する(psql)

単にテーブルの一覧を取得するだけなら、psqlでこのコマンドで可能です。※最初の記号は「¥」です。

\dt

 

実行結果はこんな感じです。

 

 

テーブル一覧とそのデータ件数を表示する

テーブルごとのデータ数を一覧表示するSQL文を紹介します。

--テーブルごとのデータ数を表示する
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;

 

ちなみにreltuples(レコード数)が大きいと「1e+06」のように表示されることがあります。

この例では、1e+06=10の6乗)=1,000,000になります。

「1e+06」のように表示したくない時は、(pg_class.reltuples / 1)と1で割ると

数値で表示されるようになると思います。(それでもかなり大きいと1eで表示されますが)

--テーブルごとのデータ数を表示する(1e+XX表示させないパターン)
SELECT 
pg_class.relname , (pg_class.reltuples / 1)
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;

 

 

Delete文を生成するSQL

テーブル一覧を取得するSQLを使い、delete文を作成するSQLです。

このSQLを投げると「delete from テーブル名;」の文が結果に出力されます。

※テーブル名で指定する場合は、最後の行のコメントアウトを外してください。

--テーブル一覧からdelete文を作成する
select 
    'delete from ' || tablename || ';' as del_sql from pg_tables  -- delete文
where schemaname not in('pg_catalog','information_schema')
--and tablename like '%テーブル名を指定%';

 

 

関連記事

https://postgresweb.com/get-columns-in-table

https://postgresweb.com/post-5371

 

SQL(SELECT文)の結果をループする方法

select結果をループするサンプルを紹介します。

大雑把にいうと1.でSQLの結果を格納する変数をrecord型で宣言し、

2.でselect結果が変数に格納されるので、それを使って処理をします。

CREATE OR REPLACE FUNCTION loop_test1()
RETURNS boolean LANGUAGE 'plpgsql'
COST 100 VOLATILE 
AS $BODY$
declare
    --1.レコード型で変数宣言
    school_rec record; 
begin
    --2.for文(select結果をschool_recに格納してループする)
    FOR school_rec IN
        --select文の最後の;は不要
        select * from m_school
    LOOP
        --ここにループ中の処理を書く
        --動作確認用:raise infoで変数の中身を表示する
        raise info '%', school_rec.school_code;
    END LOOP;

    return true;
	
end;
$BODY$;

※サンプルですので、そのまま実行してもテーブルがためエラーになります。

 

あわせて読みたい

1行の結果を取得して扱う方法はこちら

https://postgresweb.com/post-5305

 

SQLの結果を変数に格納する

関数をつくる際、select結果を変数に代入し、その後の処理に使うケースが結構あると思います。

その場合の書き方は、次のようになります。

--「列名」の値を変数へ代入する
select 列名 into 変数名 from テーブル名 where 条件文;

--実例:m_schoolのschool_nameを変数sch_nmに入れる
select school_name into sch_nm from m_school where school_code = 'a';

 

selectで取得した1行丸ごと変数に入れたいときも、書き方は変わりません。

が、結果を格納する型に注意する必要があります(後ほど説明)。

--「select *」で取得した結果を変数に入れる
select * into 変数名 from テーブル名 where 条件文;

--もちろん「select *」ではなく、列名指定でも同じ 
--一列ごとintoではなく、まとめてinto 変数名で変数に結果を格納する
select 列名 , 列名 , ・・・ into 変数名 from テーブル名 where 条件文;

--実例:m_schoolの行を変数rec_schoolに入れる
--下の方にある「関数で使う例(1行を取得して変数に格納する)」がわかりやすいと思います
select * into rec_school from m_school where school_code = 'a';

 

あわせて読みたい

この記事は1行取得した結果を変数に入れる方法です。

複数行の結果を取得して扱う方法はこちらで紹介しています。

https://postgresweb.com/post-5331

 

 

 

関数で使う例(1列を取得して変数に格納する)

上で紹介した構文を使った、実際の関数での使用例です。

CREATE OR REPLACE FUNCTION test()
RETURNS boolean 
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
declare
     
   --selectの結果を格納する変数を宣言
   sch_nm   character(10);

begin

   --m_schoolのschool_nameを変数sch_nmに入れる
   select school_name into sch_nm from m_school where school_code = 'a';

   --動作確認用(変数の中身を表示する)
   raise info '%' , sch_nm;

  -- (略)

end;
$BODY$;
ALTER FUNCTION public.test()
    OWNER TO postgres;

 

あわせて読みたい

関数の終わりにあるraiseは、変数の中を途中で確認するために使っています。

詳しくはこちら。

https://postgresweb.com/post-2852

 

 

 

 

関数で使う例(1行を取得して変数に格納する)

上で紹介した構文を使った、実際の関数での使用例です。

取得した行丸ごと、変数に入れるにはrecord型で変数宣言すると便利です。

CREATE OR REPLACE FUNCTION test2()
RETURNS boolean 
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
declare
     
   --selectの結果を格納する変数を宣言(record型で宣言)
   rec_school   record;

begin

   --実例:m_schoolの行を変数rec_schoolに入れる
   select * into rec_school from m_school where school_code = 'a';

   --動作確認用(変数の中身を表示する)
   raise info '%' , rec_school.school_name;

   -- (略)

end;
$BODY$;
ALTER FUNCTION public.test2()
    OWNER TO postgres;

IF文の書き方

PostgreSQLでのif文の書き方を紹介します。

-- 1.単純なif文
if 条件文 then
   --条件を満たすときの処理を書く
end if;

-- 2.if文とそれ以外(else)の書き方
if 条件文 then
   --条件を満たすときの処理を書く
else
   --それ以外の場合の処理を書く(else thenではない点に注意)
end if;

-- 3. AND(AかつBの時)の書き方
if (条件文1) and (条件文2) then
   --条件を満たすときの処理を書く
end if;

-- 4. OR(AまたはBの時)の書き方
if (条件文1) or (条件文2) then
   --条件を満たすときの処理を書く
end if;

-- 5.複数の条件の書き方
if 条件文1 then
   --条件1を満たすときの処理を書く
elseif 条件文2 then
   --条件2を満たすときの処理を書く
else
   --それ以外の場合の処理を書く
end if;

 

ちなみにif文は関数(FUNCTION)で使用可能ですが、SELECT文では使えません。

SELECT文ではCASE文を使います。CASE文の使い方はこちら。

 

あわせて読みたい

https://postgresweb.com/post-4229

 

 

 

IF文の書き方(実例)

if文の実例です。

-- 1.単純なif文
if num1 = 1 then
   return 0;  --変数num1が1だったら0でリターンする
end if;

-- 否定形には「!=」を使う
if num1 != 1 then
   return 0;  --変数num1が1でなければ0でリターンする
end if;

-- 大なりイコールは「>=」を使う
if num1 >= 0 then
   return true;
end if;

-- 小なりイコールは「<=」を使う
if num1 <= 0 then
   return false;
end if;

-- 2.if文とそれ以外(else)の書き方
if num1 = 1 then
   return true;   --変数num1が1だったらtrueでリターンする
else
   return false;  --変数num1が1以外ならばfalseでリターンする
end if;

-- 3. AND(AかつBの時)の書き方
if (bool1 = true) and (moji = 'abc') then
   return true;
end if;

-- 4. OR(AまたはBの時)の書き方
if (bool1 = true) and (moji = 'abc') then
   return true;
end if;

-- 5.複数の条件の書き方
if num1 = 1 then
   return 0;    --変数num1が1だったら0でリターンする
elseif num1 = 2 then
   return 1;    --変数num1が2だったら1でリターンする
else
   return -1;   --変数num1が1,2以外だったら-1でリターンする
end if;

 

変数の中身を確認する方法

if文とはほぼ関係ないですが、関数で実行中に変数の中身を確認したい場合があると思います。

それにはraiseを使う方法があり、こちらの記事で紹介しています。是非ご覧ください。

 

あわせて読みたい

https://postgresweb.com/post-2852

 

変数を宣言する方法

変数は関数で使用することができ、冒頭(declareとbeginの間)で宣言をします。

(通常のSQL問い合わせでは使用できません。)

CREATE OR REPLACE FUNCTION var_test()
RETURNS boolean 
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
declare
   --関数宣言はdeclareからbeginの間に書きます。
   --宣言は変数名+半角スペース+型です。
   variable1   integer;  
begin

 

あわせて読みたい

実行して試したい方に、関数を手っ取り早く作成する関数テンプレをこちらで紹介しています

https://postgresweb.com/template-create-function

 

 

あわせて読みたい

SQLの結果を変数に格納する方法はこちらで紹介しています

https://postgresweb.com/post-5305

 

 

 

よく使う変数

比較的よく使う変数を紹介します。

--変数名+スペース+型で宣言する
variable1   character(10);             --文字
variable2   character varying(10);     --文字
variable3   text;                      --文字
variable4   integer;                   --数値
variable5   numeric;                   --数値
variable6   boolean;                   --true/false
variable7   RECORD;                    --レコード
variable8   テーブル名%ROWTYPE;         --存在するテーブルの行
variable9   テーブル名.カラム名%TYPE;   --存在するテーブルの列
arr1        character varying[];       --配列
arr2        integer[];                 --配列

 

あわせて読みたい

関数を作っている時やデバッグ時に、変数の中身を確認したい時があると思います。

こちらの記事で、raise関数を使った変数の中身の表示方法を紹介しています。

https://postgresweb.com/post-2852

 

 

 

 

 

変数宣言と同時に代入する方法

変数宣言と同時に代入をする方法です。

-- 型の後に、コロン+イコールで値を代入します
変数名 型:=値

var1   character(10):='abc';                          --文字
var2   character varying(10):='CDE';                  --文字
var3   integer:=1;                                    --数値
var4   numeric:=2.3;                                  --数値
var5   boolean:=true;                                 --真偽値
arr1   character varying[]:=array['A','B','C','D'];   --配列
arr2   integer[]:=array[1,2,3];                       --配列

 

 

 

定数を宣言する方法

変数の次に、定数の宣言方法です。

-- 変数名の後に「constant」をつけ、型の後にコロン+イコールで定数宣言
変数名 constant 型:=値

var1 constant character varying(10) := 'abcdefghij';
var2 constant numeric := 1.08;