PostgresWeb - ポスグレウェブ

select 'abc' || 'EFG';  --'abcEFG'
select  null || 'abc';  --null（nullと結合するとnullになるため注意）

select length('abcdefgあいう');  --10（文字）

select octet_length('abcde');   --5 （半角=1、全角=3バイト）
select octet_length('ｱｲｳえお');  --15（半角ｶﾀｶﾅも1文字3バイトで計算）

select substring('abcdefghijk',2,3); -- 'bcd'  (2文字目から3文字取得する)
select left('abcdefghijk',5);        -- 'abcde'(左から5文字取得する)
select right('abcdefghijk',5);       -- 'ghijk'(右から5文字取得する)

select strpos('abcdefg','ef');                --5（=5文字目にある）
select strpos(upper('abcdefg'),upper('Cde')); --3（大文字小文字の区別なし）
select strpos('abcdefg','xyz');               --0（見つからない場合0となる）

select * from trim('   abc   ');    --'abc'  （前後のスペースを除去）
select * from trim('   a b c   ');  --'a b c'（文字間のスペースは除去されない）

select upper('abc');  --ABC（大文字へ変換）
select lower('EFG');  --efg（小文字へ変換）

select replace('everywhere','e','E');  --'EvErywhErE'（e ⇒ Eへ置換）

select lpad('12345',10,'0');  --'0000012345'（10桁前0埋め）
select lpad(cast(12345 as character varying), 8 , '0'); --"00012345"(数値をcast)

select rpad('12345',10,' ');  --'12345     '（後ろスペース埋め10桁）

select * From COALESCE(null , 'abc');  --'abc'(一つ目の引数がnullなら二つ目の引数を返す)

select to_char(12345.67, 'FM999,999.999'); --"12,345.67"  少数3桁ｶﾝﾏ区切り
select to_char(12345.67, 'FM999,999.000'); --"12,345.670" 少数3桁でｶﾝﾏ区切り

select * from abs(-5);  --5

select * from mod(11,3);  --2（11÷3の余り）

select * From round(123.45678,3);  --123.457（小数第3位四捨五入）
select * From trunc(123.45678,3);  --123.456（小数第3位切り捨て）
--切り上げを小数第1位までとする時：0.09を足して、小数第1位で切り捨てる

select * from random(); --0.896430・・・
--範囲指定でのランダム値：最小値=1,最大値=10,000の時（値は適宜変更してください）
select round(( random() * (1 - 10000) )::numeric, 0) + 10000; --7000

select * from current_date;      --"2020-06-11"
select * from current_time;      --"22:00:00.000000+09"
select * from now();             --"2020-06-11 22:00:00.000000+09"
select * from current_timestamp; --"2020-06-11 22:00:00.000000+09"
--now()とcurrent_timestampの違いはありません

select * from to_char(now(),'YYYY/MM/DD');  --2020/04/01
select * From to_char(now(), 'HH:MI:SS');   --10:34:56（12時間表記）
select * from to_char(now(), 'HH24:MI:SS'); --22:34:56（24時間表記）
select * from to_char(now(),'YYYY/MM/DD HH24:MI:SS'); --2020/04/01 22:34:56

select current_date + cast('5 months' as INTERVAL);  --"2020-11-12 00:00:00"
select current_date + cast('5 weeks' as INTERVAL);   --"2020-07-17 00:00:00"
select current_date + cast('5 days' as INTERVAL);    --"2020-06-17 00:00:00"
select current_date + cast('5 hours' as INTERVAL);   --"2020-06-12 05:00:00"
select current_date + cast('5 minutes' as INTERVAL); --"2020-06-12 00:05:00"
select current_date + cast('5 seconds' as INTERVAL); --"2020-06-12 00:00:05"

select date_part('dow',current_date); --0:日、1:月、2:火、3:水、4:木、5:金、6:土

select date(date_trunc('month',current_date) + '1 month' + '-1 day') --2020/6/30

select array_length(array['A','B','C'],1); --3 ※最後の",1"を忘れないように注意

for i in 1..array_length(arr,1) loop  --arrは配列の変数
raise info '%' , arr[i]; --ここに処理を書く（raise info：配列の中身を表示する） 
end loop;

select * from cast('20190401' as date);  --2019-04-01

select * from cast('20190401 123456' as timestamp); --2019-04-01 12:34:56

select * From cast('0123' as integer);     --　123
select * From cast('0123.45' as numeric);  --　123.45

select * From cast(12345 as character varying);  -- '12345'
select * From cast(45678 as text);               -- '45678'

select * From cast(cast(20190401 as character varying(8)) as date); --2019-04-01

/*従業員テーブルを作成し、
部署マスタ（busyo）の部署コードに対し外部キー制約を付ける（DELETE CASCADE）*/
CREATE TABLE employee
(
    employee_code character varying(10) NOT NULL,
	nm character varying(100),
	department_code  character varying(10),
	--（略）
    CONSTRAINT 主キー名 PRIMARY KEY (employee_code),  --主キー
	CONSTRAINT 外部キー名 FOREIGN KEY (department_code)    --外部キー
	REFERENCES department (department_code) ON DELETE CASCADE on update no action --DELETE CASCADE
);

--DELETE CASCADEしない時は、最後の行を「CASCADE」→「no action」にする
--例↓
--REFERENCES busyo (busyo_code) ON DELETE no action on update no action

--列の追加
ALTER TABLE テーブル名 add 列名 型;

--Not Null制約の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET NOT NULL;

--デフォルト値の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET DEFAULT デフォルト値;

--列の削除
ALTER TABLE テーブル名 DROP COLUMN 列名;

--外部キー制約の追加
ALTER TABLE テーブル名
ADD FOREIGN KEY (外部キーを付けるテーブルの列名) REFERENCES 参照先テーブル名 (参照先列名);

--インデックスの作成
CREATE INDEX インデックス名 ON テーブル名 (列名1 , 列名2 , ・・・ );

--インデックスの削除
DROP INDEX インデックス名;

COMMENT ON DATABASE データベース名 IS 'コメントです';    --データベースへコメント
COMMENT ON TABLE テーブル名 IS 'コメントです';           --テーブルへコメント
COMMENT ON COLUMN テーブル名.列名 IS 'コメントです';      --列へコメント
COMMENT ON COLUMN テーブル名.列名 IS NULL;               --コメントの削除

--生成列を作る（CREATE TABLEの列に書く）
列名 型 GENERATED ALWAYS AS 式 STORED

CREATE TABLE public.test
(   ・・・
    height numeric,
    weight numeric,
    --生成列 : BMI（ 体重　÷　身長（ｍ）の2乗）の作成
    bmi numeric(3,1) GENERATED ALWAYS AS (weight / ((height / 100) * (height / 100))) STORED,
    ・・・
)

CREATE OR REPLACE VIEW ビュー名 AS
SELECT xxx , xxx, … FROM テーブル名;

DROP VIEW ビュー名;

 --ビュー作成の構文にMATERIALIZEDがつく形
CREATE MATERIALIZED VIEW ビュー名 AS
SELECT xxx , xxx, … FROM テーブル名;

CREATE UNIQUE INDEX ON マテビュー名(列1 , 列2 , ・・・);

REFRESH MATERIALIZED VIEW ﾏﾃﾋﾞｭｰ名;              --通常のリフレッシュ文
REFRESH MATERIALIZED VIEW CONCURRENTLY ﾏﾃﾋﾞｭｰ名; --←これにはINDEX作成が必要

SELECT 
rank() OVER(PARTITION BY 列名 order by 列名 desc) AS aaa
--avg(列名)、min(列名)、max(列名)、sum(列名)等も使える　
--filterで集計する対象を指定できる。FROMの後のWHEREとは意味が異なる点に注意。
, avg(列名) filter(where 条件文) OVER(PARTITION BY 列名 order by 列名 desc) AS bbb
FROM テーブル名
WHERE XXXX --ここの条件文はSELECT文そのものの抽出条件

variable1   character(10);             --文字
variable2   character varying(10);     --文字
variable3   integer;                   --数値
variable4   numeric;                   --数値
variable5   boolean;                   --true/false
variable6   RECORD;                    --レコード
variable7   テーブル名%ROWTYPE;         --存在するﾃｰﾌﾞﾙの行
variable8   テーブル名.カラム名%TYPE;   --存在するﾃｰﾌﾞﾙの列
arr1        character varying[];       --配列
arr2        integer[];                 --配列
variable9   integer:=3;                --数値（初期値を設定）

variable1 constant character varying(10) := 'abcdefghij';
variable2 constant numeric := 1.08;  --定数にするには「constant」をつける

--1行コメント
/* 複数行のコメント1行目
   複数行のコメント2行目*/

if (a > b) and (a < c) then   --大なり小なり 
if (a >= b) or (a <= c) then　--大なりイコール、小なりイコール
if (a = b) and (a != c) then  --イコール、ノットイコール

if variable1 = 1 then
	--処理1;
elseif variable1 = 2 then
	--処理2;
else
	--処理3;
end if;
 
--否定の場合（!=をつける）
if variable1 != 1 then ・・・
 
--「select * into rec・・・」で取得した結果がない時の条件分岐例
if rec is null then ・・・

select 列名 into 変数 from テーブル名; --「列名」の値を変数へ代入する
 
--例
select emp_name into nm from m_employee where ・・・;

select * into 変数 from テーブル名;  --SELECTで取得した行を変数へ入れる
 
--例
rec record;                         --変数「rec」をrecord型で宣言(declareへ書く)
select * into rec from m_employee;  --m_employeeをSELECTした結果をrecへ代入
if rec.code = '' then …             --recの列「code」の値で条件分岐

raise info '%' , 変数名;  --変数の中身を表示する
raise info '%' , var1;    --変数var1の中身を表示する例
--変数の中身は「データ出力」の2つ右にある「メッセージ」タブに表示されます

for i in 1..10 loop --1から10まで繰り返す
--処理内容
end loop;
 
for i in 1..10 loop
	IF i > 5 THEN
		EXIT;  --「EXIT」でループを抜け出す
	END IF;
end loop;

FOR rec IN		                  --「rec」はrecord型の変数
	select * from XXXXX           -- 最後の「;」不要
LOOP
	raise info '%', rec.code;     --codeの中身を表示する
END LOOP;

FOR rec
	-- 列Aが'test'のレコードを取得しその結果をrecに入れる
	IN EXECUTE 'select * from XXX where 列A = ''test'' '
LOOP
	raise info '%', rec.code; --codeの値を表示
END LOOP;

while 条件文 loop
	--条件文がtrueの時にこの処理を繰り返す
	--処理
end loop;

CREATE OR REPLACE FUNCTION testfnc              --ファンクション名は適宜変更する
(arg1 character varying,arg2 numeric)           --引数をこの行で設定
RETURNS boolean                                 --返り値の型を定義
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$  --この行は変更しない
declare
begin
	--処理を記載
	return true;   -- 返り値はboolean型なのでここはbooleanでリターン（とりあえず）
end;
$BODY$;
ALTER FUNCTION testfnc(character varying,numeric) OWNER TO postgres;
 
--※引数のデフォルト値を設定するにはdefaultを使う
--testfnc(arg numeric default -1 )   --初期値「-1」を設定する

--上のテンプレ関数作成を実行する（上の関数を作成後、コピー＆貼り付けで実行できます）
select * From testfnc('a',1);
select testfnc('a',1);       --どちらでも同じ（結果も変わらない）

--関数「testfnc」を削除する。引数も含んで書かなければならない点に注意
--DROP FUNCTION 関数名(引数);
DROP FUNCTION testfnc(arg1 character varying,arg2 numeric);

CREATE OR REPLACE PROCEDURE testproc(                   --プロシージャ名は要変更
INOUT num1 integer,INOUT num2 integer,IN num3 integer)  --引数（下に補足あり）
LANGUAGE 'plpgsql' AS $BODY$
BEGIN
	--処理を記載
END;
$BODY$;

call testproc(1,2,3);  --プロシージャはcallを使う（selectは不可）

BEGIN
	--（略）
	EXCEPTION  --BEGINとENDの間に書く
	WHEN OTHERS THEN
	--エラーになった時の処理をここに書く
END;

CREATE FUNCTION public.trg_test()   --ファンクション名は適宜変更
RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF
AS $BODY$    
BEGIN
	--処理を記載
	return null;
END ;		
$BODY$;
ALTER FUNCTION public.trg_test() OWNER TO postgres;
--このファンクションをトリガーで起動するように設定する（2以降で）

--1.いつ、2.誰に、3.なにを定義する
CREATE TRIGGER tg_test_m_school   -- トリガー名の作成
AFTER UPDATE                      -- 1.いつ：UPDATEの後に　※α
ON public.m_school                -- 2.誰に：m_tableにトリガーを紐づける
FOR EACH ROW EXECUTE PROCEDURE public.tg_test(); --3.何を：起動するファンクション

--※αの個所は「AFTER UPDATE」ではなくBEFORE、INSERTやDELETE時にもトリガーを設定可能
BEFORE UPDATE                     -- UPDATEの前に起動する
AFTER INSERT OR DELETE OR UPDATE  -- INSERT、DELETE、UPDATEの後に起動する

--m_schoolに紐づけされているトリガーを削除する
DROP TRIGGER tg_test_m_school ON public.m_school;

--トリガーで起動するファンクションを削除
DROP FUNCTION public.tg_test();  
/*トリガーでこのファンクションが参照されている時にはエラーとなるので、
あらかじめ3のように紐づけを削除しておく（＝DROP TRIGGERする）*/

--INSERT , UPDATE , DELETEで処理を分けたい時の書き方
BEGIN
  IF TG_OP = 'UPDATE' THEN
    --UPDATE時の処理を書く
  ELSEIF TG_OP = 'INSERT' THEN
    --INSERT時の処理を書く
  ELSEIF TG_OP = 'DELETE' THEN
    --DELETE時の処理を書く
  END IF;
  return null;
END ;

old -- 更新される前の行が格納される変数（レコード型）
new -- 更新された後の行が格納される変数（レコード型）

--old.列名、new.列名で値を取得することも可能
IF TG_OP = 'UPDATE' THEN
   insert into test(hoge1)values(old.school_name);
   insert into test(hoge1)values(new.school_name);
ELSEIF TG_OP = 'INSERT' THEN
   insert into test(hoge1)values(new.school_name);
ELSEIF TG_OP = 'DELETE' THEN
   insert into test(hoge1)values(old.school_name);
END IF;

CREATE TABLE public.test(  ・・・
    hoge jsonb,             --jsonb型の列
    ・・・
)
--インデックスの作成
CREATE INDEX idx ON test ((hoge ->> '氏名'));                   
--テストデータの登録
INSERT INTO test (hoge)VALUES ('{ "連番": "1",  "氏名": "あいうえお"}');
INSERT INTO test (hoge)VALUES ('{ "連番": "2",  "氏名": "かきくけこ"}');
INSERT INTO test (hoge)VALUES ('{ "連番": "3",  "氏名": "さしすせそ"}');
--データ取得
SELECT * FROM test where hoge ->> '氏名' = 'あいうえお';

CREATE TABLE public.test(  ・・・
    列名 inet  -- ex.192.168.1.0/24
    ・・・
)

CREATE TABLE public.test(  ・・・  
    hoge1 integer[]             --値の例：{1,2,3,4,5}
    hoge2 character varying[]   --値の例：{'ab','cde','fghi'}
)

--列挙型を定義
CREATE TYPE color AS ENUM ('RED', 'YELLOW', 'BLUE');

--テーブル作成時に列挙型を使用
CREATE TABLE car (
    code character varying(8),
    name character varying(120),
    color color
);

--データ登録
INSERT INTO car VALUES('001','A車','RED');   --成功
INSERT INTO car VALUES('002','B車','GREEN'); --エラー

--列挙型を削除
DROP TYPE color; --すでに使われている場合はエラーとなる

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

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

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

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

--シーケンス一覧の取得（そのまま実行してみてください）
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に設定

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

--テーブルごとのデータ数を表示する（そのまま実行してみてください）
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;

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

-- 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;

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

--次のselect文でvacuumの実行時間を確認できる
select relname , last_vacuum , last_autovacuum from pg_stat_all_tables;

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

--次のselect文でanalyzeの実行時間を確認できる
--autovacuum時にanalyzeが実行されるが、その時間がautoanalyzeとなる
select relname , last_analyze , last_autoanalyze from pg_stat_all_tables;

CLUSTER テーブル名 USING インデックス名;

EXPLAIN SELECT文;
EXPLAIN ANALYZE SELECT文;

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

--pidはこの上の『DBに接続しているセッションの確認』で確認する
SELECT pg_terminate_backend(pidを設定); 

psql -h localhost -p 5432 -U postgres -d データベース名

CREATE ROLE ロール名 WITH LOGIN PASSWORD 'password'; --PostgreSQLではユーザーをロールという

DROP ROLE ロール名; 
--権限が割り当てによりエラーが発生する時、次（↓）の特権削除をする

DROP OWNED BY ロール名;

GRANT SELECT ON テーブル名 TO ロール名;          --SELECT権限を付与する
GRANT SELECT ON ビュー名 TO ロール名;            --SELECT権限を付与する
GRANT ALL PRIVILEGES ON テーブル名 To ロール名;  --すべての権限を付与する

REVOKE ALL PRIVILEGES ON テーブル名 FROM ロール名;
REVOKE ALL PRIVILEGES ON ビュー名 FROM ロール名;

--サービスの開始
net start postgresql-XXXXX
net start postgresql-x64-12  --PostgreSQL12の場合
--サービスの停止
net stop postgresql-XXXXX
net stop postgresql-x64-12   --PostgreSQL12の場合
--サービスの再起動（停止 → 起動の順番に実行する）
net stop postgresql-x64-12   --PostgreSQL12の場合
net start postgresql-x64-12

--pg_dump.exeのありかまで、cdで移動（パスが通っている場合は不要）
cd C:\Program Files\PostgreSQL\bin
pg_dump -U ユーザー名 -f バックアップファイルのフルパス（拡張子は.backup） -Fc データベース名

--１．あらかじめ作っておいた空のデータベースにリストアする
pg_restore -U postgres -d データベース名 "バックアップファイルパス" 
--２．リストアと同時にデータベースを作成する（作られるDB名はバックアップと同じになる）
pg_restore -U ユーザー名 -C -d postgres "バックアップファイルパス"

--postgreSQLにログイン後（psqlで）実行します
¥copy テーブル名 from 'CSVファイルのフルパス' with csv         --CSVにヘッダ行がない場合
¥copy テーブル名 from 'CSVファイルのフルパス' with csv header  --CSVにヘッダ行がある場合

log_destination = 'strderr' -- stderr(標準ログ), csvlog, syslogから選択する
logging_collector = on      -- on
log_min_duration_statement = 1000 -- 遅いSQLをログに出力する（単位はミリ秒）
log_line_prefix = '[%m][%u][%d][%h][%p][%a][%e]' --ログに出力する情報