【PostgreSQL】よく使う構文・コマンドを一覧にまとめました~初心者から上級者まで~【2020年版】
入門用にも!基本構文をまとめてみました
PostgreSQLの基本構文やSQLをまとめたページです(2020年、PostgreSQL12対応)。
基本的なことから、こんな時どうしったけ?ということが解決できるサイトを目指します。
※ここにないものも、こちらにあるかもしれないので一度見てみて下さい。
文字列操作
1.文字列の結合
select 'abc' || 'EFG'; --'abcEFG'
select null || 'abc'; --null(nullと結合するとnullになるため注意)
※ nullの置換はこの下にある「11.Nullの場合の処理(COALESCE)」を参考にして下さい。
2.文字数の取得(length)
select length('abcdefgあいう'); --10(文字)
3.文字列のバイト数を取得(octet_length)自作関数
select octet_length('abcde'); --5 (半角=1、全角=3バイト)
select octet_length('アイウえお'); --15(半角カタカナも1文字3バイトで計算)
4.文字列の一部分を取得(substring、left、right)詳細・補足
select substring('abcdefghijk',2,3); -- 'bcd' (2文字目から3文字取得する)
select left('abcdefghijk',5); -- 'abcde'(左から5文字取得する)
select right('abcdefghijk',5); -- 'ghijk'(右から5文字取得する)
5.検索文字位置の取得(strpos)詳細・補足
select strpos('abcdefg','ef'); --5(=5文字目にある)
select strpos(upper('abcdefg'),upper('Cde')); --3(大文字小文字の区別なし)
select strpos('abcdefg','xyz'); --0(見つからない場合0となる)
6.スペース除去(trim)
select * from trim(' abc '); --'abc' (前後のスペースを除去)
select * from trim(' a b c '); --'a b c'(文字間のスペースは除去されない)
7.大文字・小文字の変換(upper、lower)
select upper('abc'); --ABC(大文字へ変換)
select lower('EFG'); --efg(小文字へ変換)
8.置換(replace)
select replace('everywhere','e','E'); --'EvErywhErE'(e ⇒ Eへ置換)
9.前0埋め(lpad)
詳細・補足
select lpad('12345',10,'0'); --'0000012345'(10桁前0埋め)
select lpad(cast(12345 as character varying), 8 , '0'); --"00012345"(数値をcast)
10.後ろスペース埋め(rpad)
詳細・補足
select rpad('12345',10,' '); --'12345 '(後ろスペース埋め10桁)
11.Nullの場合の処理(COALESCE)
詳細・補足
select * From COALESCE(null , 'abc'); --'abc'(一つ目の引数がnullなら二つ目の引数を返す)
12.ひらがな⇔カタカナ、全角⇔半角の変換(自作関数)
ひらがな→カタカナ カタカナ→ひらがな 半角→全角 全角→半角
数値操作
1.数値の書式設定(to_char)
詳細・補足
select to_char(12345.67, 'FM999,999.999'); --"12,345.67" 少数3桁カンマ区切り
select to_char(12345.67, 'FM999,999.000'); --"12,345.670" 少数3桁でカンマ区切り
2.絶対値を取得(abs)
select * from abs(-5); --5
3.割り算の余りを取得(mod)
select * from mod(11,3); --2(11÷3の余り)
4.四捨五入(round、trunc)
自作関数
select * From round(123.45678,3); --123.457(小数第3位四捨五入)
select * From trunc(123.45678,3); --123.456(小数第3位切り捨て)
--切り上げを小数第1位までとする時:0.09を足して、小数第1位で切り捨てる
5.ランダム値(random)自作関数
select * from random(); --0.896430・・・
--範囲指定でのランダム値:最小値=1,最大値=10,000の時(値は適宜変更してください)
select round(( random() * (1 - 10000) )::numeric, 0) + 10000; --7000
6.こちらもチェック(自作関数)
数値であるかのチェック(IsNumeric) 四捨五入(Round)
日付操作
1.現在日時の取得
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の違いはありません
2.日付の書式設定(to_char)
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
3.月、週、日、時刻を加算する
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"
4.曜日を取得する(date_part)
select date_part('dow',current_date); --0:日、1:月、2:火、3:水、4:木、5:金、6:土
5.月末の日を取得する
select date(date_trunc('month',current_date) + '1 month' + '-1 day') --2020/6/30
6.こちらもチェック(自作関数)
日付であるかのチェック(IsDate) 西暦から和暦へ変換する 次の○曜日を取得
配列
1.配列の要素数(array_length)詳細・補足
select array_length(array['A','B','C'],1); --3 ※最後の",1"を忘れないように注意
2.配列の要素数分ループ(関数で使用)
for i in 1..array_length(arr,1) loop --arrは配列の変数
raise info '%' , arr[i]; --ここに処理を書く(raise info:配列の中身を表示する)
end loop;
型の変換(cast)
1.文字⇒日付詳細・補足
select * from cast('20190401' as date); --2019-04-01
2.文字⇒timestamp
select * from cast('20190401 123456' as timestamp); --2019-04-01 12:34:56
3.文字⇒数値
select * From cast('0123' as integer); -- 123
select * From cast('0123.45' as numeric); -- 123.45
4.数値⇒文字
select * From cast(12345 as character varying); -- '12345'
select * From cast(45678 as text); -- '45678'
5.数値⇒日付
select * From cast(cast(20190401 as character varying(8)) as date); --2019-04-01
テーブルの作成・列の変更
1.CREATE TABLE(+DELETE CASCADE)
/*従業員テーブルを作成し、
部署マスタ(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
2.列の追加・変更・削除
--列の追加
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 インデックス名;
3.データベース・テーブル・列にコメントを付加する
COMMENT ON DATABASE データベース名 IS 'コメントです'; --データベースへコメント
COMMENT ON TABLE テーブル名 IS 'コメントです'; --テーブルへコメント
COMMENT ON COLUMN テーブル名.列名 IS 'コメントです'; --列へコメント
COMMENT ON COLUMN テーブル名.列名 IS NULL; --コメントの削除
4.生成列(自動で計算される列)
--生成列を作る(CREATE TABLEの列に書く)
列名 型 GENERATED ALWAYS AS 式 STORED
CREATE TABLE public.test
( ・・・
height numeric,
weight numeric,
--生成列 : BMI( 体重 ÷ 身長(m)の2乗)の作成
bmi numeric(3,1) GENERATED ALWAYS AS (weight / ((height / 100) * (height / 100))) STORED,
・・・
)
ビュー・マテリアライズドビュー・ウィンドウ関数
1.ビューの作成
CREATE OR REPLACE VIEW ビュー名 AS
SELECT xxx , xxx, … FROM テーブル名;
2.ビューの削除
DROP VIEW ビュー名;
3.マテリアライズドビューの作成
--ビュー作成の構文にMATERIALIZEDがつく形
CREATE MATERIALIZED VIEW ビュー名 AS
SELECT xxx , xxx, … FROM テーブル名;
4.マテリアライズドビューのINDEX作成
CREATE UNIQUE INDEX ON マテビュー名(列1 , 列2 , ・・・);
5.マテリアライズドビューの更新
REFRESH MATERIALIZED VIEW マテビュー名; --通常のリフレッシュ文
REFRESH MATERIALIZED VIEW CONCURRENTLY マテビュー名; --←これにはINDEX作成が必要
6.ウィンドウ関数
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文そのものの抽出条件
関数・プロシージャ
1.変数の宣言
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; --数値(初期値を設定)
2.定数(constant)の宣言
variable1 constant character varying(10) := 'abcdefghij';
variable2 constant numeric := 1.08; --定数にするには「constant」をつける
3.1行コメント、複数行コメント
--1行コメント
/* 複数行のコメント1行目
複数行のコメント2行目*/
4.比較演算子
if (a > b) and (a < c) then --大なり小なり
if (a >= b) or (a <= c) then --大なりイコール、小なりイコール
if (a = b) and (a != c) then --イコール、ノットイコール
5.IF文
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 ・・・
6.SELECT結果を変数へ代入する①
select 列名 into 変数 from テーブル名; --「列名」の値を変数へ代入する
--例
select emp_name into nm from m_employee where ・・・;
7.SELECT結果を変数へ代入する②
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」の値で条件分岐
8.変数の中身を表示する(raise)
raise info '%' , 変数名; --変数の中身を表示する
raise info '%' , var1; --変数var1の中身を表示する例
--変数の中身は「データ出力」の2つ右にある「メッセージ」タブに表示されます
9.For文(単純なループ)
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;
10.For文(SELECT文の結果をループする方法①)
FOR rec IN --「rec」はrecord型の変数
select * from XXXXX -- 最後の「;」不要
LOOP
raise info '%', rec.code; --codeの中身を表示する
END LOOP;
11.For文(SELECT文の結果をループする方法②)
FOR rec
-- 列Aが'test'のレコードを取得しその結果をrecに入れる
IN EXECUTE 'select * from XXX where 列A = ''test'' '
LOOP
raise info '%', rec.code; --codeの値を表示
END LOOP;
12.While文
while 条件文 loop
--条件文がtrueの時にこの処理を繰り返す
--処理
end loop;
13.関数(FUNCTION)の作成・書き方 テンプレ用
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」を設定する
14.関数の実行の仕方
--上のテンプレ関数作成を実行する(上の関数を作成後、コピー&貼り付けで実行できます)
select * From testfnc('a',1);
select testfnc('a',1); --どちらでも同じ(結果も変わらない)
15.関数を削除する
--関数「testfnc」を削除する。引数も含んで書かなければならない点に注意
--DROP FUNCTION 関数名(引数);
DROP FUNCTION testfnc(arg1 character varying,arg2 numeric);
16.プロシージャ(PROCEDURE)の作成・書き方 テンプレ用
CREATE OR REPLACE PROCEDURE testproc( --プロシージャ名は要変更
INOUT num1 integer,INOUT num2 integer,IN num3 integer) --引数(下に補足あり)
LANGUAGE 'plpgsql' AS $BODY$
BEGIN
--処理を記載
END;
$BODY$;
引数:IN=引数のみ、INOUT=引数+返り値としても使用される ※OUTのみは不可
17.プロシージャの実行の仕方
call testproc(1,2,3); --プロシージャはcallを使う(selectは不可)
18.EXCEPTION
BEGIN
--(略)
EXCEPTION --BEGINとENDの間に書く
WHEN OTHERS THEN
--エラーになった時の処理をここに書く
END;
トリガー
1.トリガーファンクションの作成
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以降で)
↑は『〇〇した時にする処理』(=処理する内容)を作る。
↓は『〇〇した時』(処理するタイミング)を指定する
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の後に起動する
3.トリガーの紐づけを削除
--m_schoolに紐づけされているトリガーを削除する
DROP TRIGGER tg_test_m_school ON public.m_school;
4.トリガーの紐づけを削除
--トリガーで起動するファンクションを削除
DROP FUNCTION public.tg_test();
/*トリガーでこのファンクションが参照されている時にはエラーとなるので、
あらかじめ3のように紐づけを削除しておく(=DROP TRIGGERする)*/
5.トリガーファンクションの条件文
--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 ;
6.トリガーファンクション中の変数
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;
その他の型
1.JSONB型
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 ->> '氏名' = 'あいうえお';
2.ネットワークアドレス型
CREATE TABLE public.test( ・・・
列名 inet -- ex.192.168.1.0/24
・・・
)
3.配列型
CREATE TABLE public.test( ・・・
hoge1 integer[] --値の例:{1,2,3,4,5}
hoge2 character varying[] --値の例:{'ab','cde','fghi'}
)
4.列挙型
--列挙型を定義
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; --すでに使われている場合はエラーとなる
データベースやテーブル情報の取得
1.テーブル一覧の取得
--データベースに存在するテーブルの取得(そのまま実行してみてください)
select tablename from pg_tables
where schemaname != 'pg_catalog' order by tablename;
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に設定
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;
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;
メンテナンス
1.手動VACUUM
VACUUM テーブル名;
VACUUM; --テーブル名を省略すると、すべてのテーブルを対象とする
--次のselect文でvacuumの実行時間を確認できる
select relname , last_vacuum , last_autovacuum from pg_stat_all_tables;
2.手動ANALYZE
ANALYZE テーブル名;
ANALYZE; --テーブル名を省略すると、すべてのテーブルを対象とする
--次のselect文でanalyzeの実行時間を確認できる
--autovacuum時にanalyzeが実行されるが、その時間がautoanalyzeとなる
select relname , last_analyze , last_autoanalyze from pg_stat_all_tables;
3.CLUSTER
CLUSTER テーブル名 USING インデックス名;
4.EXPLAIN , EXPLAIN ANALYZEExplainの見方はこちら
EXPLAIN SELECT文;
EXPLAIN ANALYZE SELECT文;
5.DBに接続しているセッションの確認
select * from pg_stat_activity where datname = 'データベース名';
6.接続中セッションの切断
--pidはこの上の『DBに接続しているセッションの確認』で確認する
SELECT pg_terminate_backend(pidを設定);
コマンド
1.PostgreSQLへのログイン
psql -h 接続先 -p ポート番号 -U ユーザー名 -d データベース名
psql -h localhost -p 5432 -U postgres -d dbname
2.ユーザー(=ロール)の作成
CREATE ROLE ロール名 WITH LOGIN PASSWORD 'password'; --PostgreSQLではユーザーをロールという
3.ユーザーの削除
DROP ROLE ロール名;
--権限が割り当てによりエラーが発生する時、次(↓)の特権削除をする
4.ユーザーに関連付けられている特権を削除する
DROP OWNED BY ロール名;
5.ロールにテーブル、ビューの権限を付与
GRANT SELECT ON テーブル名 TO ロール名; --SELECT権限を付与する
GRANT SELECT ON ビュー名 TO ロール名; --SELECT権限を付与する
GRANT ALL PRIVILEGES ON テーブル名 To ロール名; --すべての権限を付与する
6.ロールにテーブル、ビューの権限を削除
REVOKE ALL PRIVILEGES ON テーブル名 FROM ロール名;
REVOKE ALL PRIVILEGES ON ビュー名 FROM ロール名;
7.PostgreSQLサービスの開始・停止・再起動
--サービスの開始
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
「アクセスが拒否されました」の時は、コマンドプロンプトを管理者として実行して下さい。
8.バックアップpg_dumpでパスワードを省略する方法
--pg_dump.exeのありかまで、cdで移動(パスが通っている場合は不要)
cd C:\Program Files\PostgreSQL\bin
pg_dump -U ユーザー名 -f バックアップファイルのフルパス(拡張子は.backup) -Fc データベース名
9.リストア
--1.あらかじめ作っておいた空のデータベースにリストアする
pg_restore -U postgres -d データベース名 "バックアップファイルパス"
--2.リストアと同時にデータベースを作成する(作られるDB名はバックアップと同じになる)
pg_restore -U ユーザー名 -C -d postgres "バックアップファイルパス"
10.CSVからデータを登録する
--postgreSQLにログイン後(psqlで)実行します
¥copy テーブル名 from 'CSVファイルのフルパス' with csv --CSVにヘッダ行がない場合
¥copy テーブル名 from 'CSVファイルのフルパス' with csv header --CSVにヘッダ行がある場合