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

/*従業員テーブルを作成し、
部署マスタ（department）の部署コードに対し外部キー制約を付ける（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

--table1
CREATE TABLE table1
(
	cd1 integer not null,
	nm1 text,
	CONSTRAINT pk_table1 PRIMARY KEY (cd1)
);

--table2
CREATE TABLE table2
(
	cd2 integer not null,
	cd1 integer,
	nm2 text,
	CONSTRAINT pk_table2 PRIMARY KEY (cd2),
	CONSTRAINT fk_tabel2_table1 FOREIGN KEY (cd1)
	REFERENCES table1 (cd1) 
	ON UPDATE NO ACTION
	ON DELETE CASCADE
);

--テストデータ用(使用する場合はコメント解除）
/*
insert into table1 values(1,'A');
insert into table1 values(2,'B');
insert into table1 values(3,'C');
insert into table2 values(1,1,'a');
insert into table2 values(2,1,'b');
insert into table2 values(3,2,'c');
insert into table2 values(4,2,'d');
insert into table2 values(5,3,'e');
insert into table2 values(6,3,'f');
*/

--列の追加
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文そのものの抽出条件

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;

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

--上のテンプレ関数作成を実行する（上の関数を作成後、コピー＆貼り付けで実行できます）
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)  --引数（下に補足あり）
--引数：IN＝引数のみ、INOUT＝引数＋返り値としても使用される　※OUTのみは不可
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 trg_test_m_school            -- トリガー名の作成
AFTER UPDATE                                -- 1.いつ：UPDATEの後に　※α
ON public.m_school                          -- 2.誰に：m_tableにトリガーを紐づける
FOR EACH ROW EXECUTE PROCEDURE trg_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;

--データベースに存在するテーブルの取得（そのまま実行してみてください）
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 接続先 -p ポート番号 -U ユーザー名 -d データベース名   -- Uは大文字のため注意

-- コマンド例
psql -h localhost -p 5432 -U postgres -d dbname
psql -h testserver -p 5432 -U postgres -d dbname   -- testserverはサーバー名
psql -h x.x.x.x -p 5432 -U postgres -d dbname      -- x.x.x.xはIPアドレス

-- オプションを指定せず接続も可能。ただしユーザー名とデータベースの順番が上と逆になる点に注意
psql データベース名 ユーザー名
psql dbname postgres

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にヘッダ行がある場合

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なら二つ目の引数を返す)