PostgreSQL構文 | PostgresWeb - ポスグレウェブ

【PostgreSQL】検索文字位置の取得（strpos）

検索文字位置を取得する

文字列の中から、検索文字が何文字目にあるか検索するにはstrposを使用します。

見つからなかった場合は0が、見つかった場合は1以上の数値が返ります。

strpos( 検索対象の文字列 , 検索する文字列)

使用例

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

ちょっとだけ緩く比較する

strposでは大文字小文字が区別されるので、区別しない＋前後スペース除外した例を示します。

--検索対象と検索する文字列をそれぞれ、upperとtrimをかけて検索する
select strpos(trim(upper('ABCDEFGHIJKLMN')),trim(upper(' efg ')));  --5

【PostgreSQL】upper（小文字を大文字に変換する）

小文字を大文字に変換する

小文字を大文字に変換するには、upperを使用します。

-- 小文字を大文字に変換する
upper( 変換したい文字列 )

実行例

upperの使用例です。※下の例はそのまま実行できます

-- 使用例 
select upper('abc');      --"ABC"
select upper('aBc');      --"ABC"
select upper('ａｂｃ');   --"ＡＢＣ"

注意点

全角小文字が大文字にならない時、ロケールの設定が「C」になっている可能性があります。（ロケールが「C」の時は、全角小文字がupperされません。）

その場合は、translateを使用して1文字ずつ変換すれば解決できます。

-- ロケールが「C」の時は、全角小文字がupperされない
select upper('ａｂｃ');   --"ａｂｃ"

-- translateを使用することで変換ができるようになります。
select translate('ａｂｃ', 'ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ', 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ');   --"ＡＢＣ"

【PostgreSQL】For文（ループ文）の書き方、ループから抜ける方法

For文（ループ文）の書き方

PostgreSQLにおけるFor文の書き方です。

FOR 変数名 IN 初期値..終了値 LOOP
  --処理をここに書く
END LOOP;

あわせて読みたい

使用例

--1から10まで繰り返す
for i in 1..10 loop
	raise info '%' , i;
end loop;

※使用しているraise文はこちらで説明しています。

変数の値を画面に出す（raise）

ループ文の抜け方

ループ文から途中で抜けるには、EXITを使います。

--1から10まで繰り返す
for i in 1..10 loop

    --ループを抜けるIF文
	if i = 5 then
		exit; --exitでループを抜ける
	end if;

	raise info '%' , i;
end loop;

【PostgreSQL】変数の値を画面に出す（raise）【デバッグ用にも】

変数の値を画面に出す（raise）

FUNCTIONを作っているときに、変数の中身を見たい時があるとおもいますが、

その中身を表示するにはRaiseを使います。

-- 変数の中身を表示する（「%」に変数の値がセットされます）
raise info '%' , 変数名;　

-- 例
raise info '%' , num1;        -- 変数num1の中身を表示する
raise info 'num1=%' , num1;   -- このように書くと「num1=XX」と表示し、見やすくなります

変数の中身が表示された様子

変数の中身は「メッセージタブ」に表示されます。

「INFO：」の右に表示されているのが変数の値です。

※ selectしている「raise_test()」関数はこの次で紹介しています。

raise動作確認用の関数

raiseの動作を確認できるような関数を作成しました。（コピー＆pgAdminに貼り付け→実行でOKです）

実行しても結果はnullが返りますが、メッセージタブに値が表示されているのが確認できます。

numの値を変えて実行すると、よりわかりやすくなると思いますのでお試しを。

CREATE OR REPLACE FUNCTION raise_test()
RETURNS character varying  LANGUAGE 'plpgsql'
COST 100 VOLATILE AS $BODY$
declare
	num numeric;
BEGIN

	num = 100; 
	raise info '%' , num; --変数numの中身を表示
	
 	num = 200; 
	raise info '%' , num; --変数numの中身を表示
	
 	num = 300; 
	raise info '%' , num; --変数numの中身を表示
	
	return null;

END;
$BODY$;

--実行するには次の文を実行して下さい
select * from raise_test();

あわせて読みたい

https://postgresweb.com/post-5260

【PostgreSQL】関数（FUNCTION）の作り方・書き方を解説する

関数（FUNCTION）の作り方・書き方を解説する

ここでは関数（FUNCTION）を作る場合の書き方について、解説します。

ざっくりと各ブロックはこんな感じになります。（黄色のところを書くイメージ）

①～⑤の個所について説明していきます。

※ここで紹介している以外の書き方もありますが、一つの書き方として紹介します。

CREATE OR REPLACE FUNCTION 関数の名前(引数1 , 引数2 , 引数3 , ・・・) … ①

RETURNS 戻り値の型 AS … ②

$BODY$

declare

引数を宣言する部分 … ③

BEGIN

処理を書く部分 … ④

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

【①の箇所】関数の名前

①の黄色の箇所は関数名と引数の型を記述するところです。

CREATE OR REPLACE FUNCTION 関数の名前(引数1 , 引数2 , 引数3 , ・・・)

例：

CREATE OR REPLACE FUNCTION testfnc( character varying , numeric , numeric)

【②の箇所】戻り値

RETURNS 戻り値の型 AS … ②

例：

RETURNS character varying AS　　… 文字型で返す

RETURNS integer AS　　　　　　… 数値型で返す

【③の箇所】引数

引数は複数あった場合、「, 」（カンマ）でつなげて書きます。

CREATE OR REPLACE FUNCTION test_fnc(引数1 , 引数2 , 引数3)

例：

CREATE OR REPLACE FUNCTION test_fnc(character varying)　　　　　　　　　 … 引数１つ

CREATE OR REPLACE FUNCTION test_fnc(numeric , character varying)　　　　　… 引数２つ

CREATE OR REPLACE FUNCTION test_fnc(character varying , numeric , integer)　… 引数３つ

BEGINの前に引数を格納する変数名を書きます。

水色の「alias for $XX」でXX番目の引数かを書きます。

CREATE OR REPLACE FUNCTION 関数の名前(引数1 , 引数2 , 引数3 , ・・・)

・・・

declare

hensuu1 alias for $1; … 引数1の値はこの変数に代入される … ③

hensuu2 alias for $2; … 引数2の値はこの変数名に入される

hensuu3 alias for $3; … 引数3の値はこの変数名に入される

BEGIN

・・・

【④の箇所】処理

④処理部分は目的に合わせた処理を記述しますが、最後は「return」で返り値を指定して下さい。

BEGIN

処理を書く部分

return 返り値;

END;

例：

BEGIN

（中略）

return 0;

END;

関数のテンプレート

上で紹介はしたものの、1から関数を書くのは手間です。

そのためコピー＆貼り付けである程度済む、「関数テンプレート」を用意したので使ってみて下さい。

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

【PostgreSQL】自作関数の実行の仕方

自作関数の実行の仕方

１～５の順番で実行してください。（詳しくは画像を参照）

１．自作関数のページからコードをコピーする

２．コピーしたコードをpgAdminの「クエリツール」へ貼り付け

３．貼り付けたコードを実行

４．自作関数のページから「実行例」のコードをコピーする

５．コピーしたコードをpgAdminの「クエリツール」へ貼り付け実行し確認する

以上が実行する手順です。

【PostgreSQL】文字を日付に変換する、書式を設定する（cast、to_char）

文字を日付に変換する

型を変換するにはcast（キャスト）を使用します。

日付への変換には「date」や「timestamp」を指定します。

-- 型を変換する
cast( A as B )  -- Aを型Bに変換する

-- 文字列を日付（date）に変換する
cast( 日付に変換する文字列 as date )

-- 文字列を日付時刻（timestamp）に変換する
cast( 日付に変換する文字列 as timestamp )

-- 書式を設定する
to_char(日付・日付時刻, 書式)

あわせて読みたい

書式設定で使用しているto_char()はこちらで紹介しています

https://postgresweb.com/post-4975

文字列を日付に変換する例

文字列 → 日付に変換する例

-- 1.文字列「20190401」を日付に変換する 
select cast('20190401' as date);              -- 2019-04-01 

-- 2.文字列「20190401」をtimestampに変換する 
select cast('20190401' as timestamp);         -- 2019-04-01 00:00:00 

-- 3.文字列「20190401 123456」をtimestampに変換する 
select cast('20190401 123456' as timestamp);  -- 2019-04-01 12:34:56

文字列 → 日付に変換＋書式設定をする例

-- 1.「20190401」をdateに変換し、「YYYY/MM/DD」形式へ変換する
select to_char(cast('20190401' as date), 'YYYY/MM/DD');  
-- 2019/04/01 

-- 2.「20190401 123456」をtimestampに変換し、「HH24:MI:SS」形式へ変換する
select to_char(cast('20190401 123456' as timestamp), 'HH24:MI:SS'); 
-- 12:34:56

-- 3.「20190401 123456」をtimestampに変換し、「YYYY/MM/DD HH24:MI:SS」形式へ変換する
select to_char(cast('20190401 123456' as timestamp), 'YYYY/MM/DD HH24:MI:SS'); 
-- 2019/04/01 12:34:56

数値文字列を「yyyy/mm/dd」形式にする例

-- 「20190401」を、「YYYY/MM/DD」形式へ変換する
select left('20190401',4) 
       || '/' || substring('20190401',5,2) 
       || '/' || right('20190401',2) ;
-- 2019/04/01

あわせて読みたい

https://postgresweb.com/post-368

数値を日付に変換する

数値 → 日付に変換する例です。

数値→日付へ直接変換するとエラーとなるため、数値→文字→dateの順で変換しています。

-- 1.数値「20190401」を日付に変換する
select cast(cast(20190401 as character varying(8)) as date);  
-- 2019-04-01

-- 2.数値「20190401123456」を日付と時間に変換する（2行で1つの行です）
select cast(left(cast(20190401123456 as character varying(14)),8) || ' ' || 
       right(cast(20190401123456 as character varying(14)),6) as timestamp);
-- 2019-04-01 12:34:56

【PostgreSQL】前0埋め、後ろ0埋め（lpad、rpad）

構文

--前0埋め（ lpad ）
lpad( 0埋めしたい文字列 , 何文字にするか ,'0')

--後ろ0埋め（ rpad ）
rpad( 0埋めしたい文字列 , 何文字にするか ,'0')

--数値の場合は文字に変換した後にlpad、rpadします
lpad( cast(0埋めしたい数値 as character varying) , 何文字にするか ,'0')

最後の引数『'0'』のところを別の文字に変えると、その文字で埋めるようになります。

「何文字にするか」は0からではなく、1からカウントします。

前0埋め

--前0埋め8桁
select lpad( '12345' , 8 , '0');    --結果："00012345"

--前0埋め10桁
select lpad( '12345' , 10 , '0');   --結果："0000012345"

--前スペース埋め10桁
select lpad( '12345' , 10 , ' ');   --結果："     12345"

--数値の0埋め(数値の先頭に文字を付けるとエラーになるため、文字列にcastしています）
select lpad( cast(12345 as character varying), 8 , '0');  --結果："00012345"

あわせて読みたい

https://postgresweb.com/post-342

後ろ0埋め

--後ろ0埋め8桁
select rpad( '12345' , 8 , '0');    --結果："12345000"

--後ろ0埋め10桁
select rpad( '12345' , 10 , '0');   --結果："1234500000"

--後ろスペース埋め10桁
select rpad( '12345' , 10 , ' ');   --結果："12345     "

--数値の0埋め(castを使用）
select rpad( cast(12345 as character varying), 8 , '0');  --結果："12345000"

【PostgreSQL】Nullの時に別の値に置き換える（COALESCE）

Nullの時に置換する方法

文字列がNullの時、別の文字列に置き換えるにはCOALESCEを使用します。

※Nullでない時は、『Nullチェックする文字列』がそのまま返ります。

（OracleでのNVL、SQL ServerでのISNULL、MySQLでのCOALESCEに相当します）

--Nullの時、別の文字に置換する
COALESCE( Nullチェックする文字列 , Nullの場合に置換する文字 )

使用例

COALESCEを使った例を紹介します。　※次の例はそのままコピー＆貼り付けで実行できます

--次の例は、チェックする文字列がnullのため'abc'が返ります 
select * From COALESCE(null , 'abc');   --【結果】:'abc' 

--次の例は、チェックする文字列がnullでないため'xyz'が返ります 
select * From COALESCE('xyz' , 'abc');  --【結果】:'xyz'

合計値、平均値を求める例

nullが含まれる（かもしれない）列を使って合計、平均値を求める例を紹介します。

--数値列がnullなら0に変換して合計値を求める 
select sum(coalesce(数値列,0)) from テーブル名;

--nullのデータは無視して平均値を求める
--※nullを「-1」に変換し、「-1」を無効値として除外する
select avg(coalesce(数値列,0)) from テーブル名
where coalesce(数値列,-1) != -1;

その他の文字列操作

・文字列の結合、nullの結合

・改行コードで結合、置換する

・文字数のカウント（length）、バイト数を取得する

・文字の切り取り（Left、Right、Substring）

・検索文字位置の取得（strpos）

・全角半角スペースを除去する（trim）

・upper（小文字を大文字に変換する）

・大文字小文字を変換する、区別せず比較する

・文字を置換する、複数置換する（replace、translate）

・前0埋め、後ろ0埋め（lpad、rpad）

【PostgreSQL】GROUP BY（グループ化）【DB入門】

GROUP BYとは

GROUP BYとはグループ化を行うときに使う構文です。

グループ化とは、例えば学校を例すると、

・クラスごとに合計点を計算する

・学年ごとに平均点を計算する

・教科ごとに最高点、最低点を計算する

等々、○○ごとに集計をするために使います。

集約関数の種類

求めたいものによって、SUMやMAXという関数（集約関数といいます）を使います。

SUM　 …　合計を求める

MAX　…　最大値を求める

MIN　…　最小値を求める

AVG　…　平均を求める

COUNT　…　行数をカウントする

GROUP BYの構文と注意

GROUP BYの構文としてはこのような形になります。

--GROUP BYの構文
SELECT 列名1 , SUM(列名2) , AVG(列名3) 
FROM テーブル名 
GROUP BY 列名1; --列名1でグループ化する

GROUP BYの注意点は、SELECTからFROMまでの間に書く列名は、GROUP BYの後に書いてある列名だけそのまま書くことができるという点です。

上の構文では、『GROUP BY 列名1』となっているため、『SELECT 列名1 FROM テーブル名』と書くことができます。逆にいうと、列名2と列名3はGROUP BYの後ろに書かれていないため、集約関数とともに書かないといけません。そのため列名2 , 3は『SUM(列名2) , AVG(列名3)』となっています。

GROUP BYの注意（並び替え）

GROUP BYでは、集計をかけつつ並び替えも可能です。

（例えば、クラス別の合計点を計算し、合計点の高い順に並べるなど）

--ORDER BY（並び順）のSQL例
SELECT 列名1 , SUM(列名2) , AVG(列名3) 
FROM テーブル名 
GROUP BY 列名1 
ORDER BY 列名1 , SUM(列名2);

ORDER BYの後も基本的には『SELECT～FROM』の決まりと変わりありません。つまり列名2と列名3はGROUP BYの後ろに書かれていないため、ORDER BYでも集約関数とともに書かないといけません。以上が注意点になります。

SUM…合計を求める

SUMは合計値を求める関数です。

--合計を求める構文（列名2でグループ化、列名1を合計する）
select SUM(列名1) 
from テーブル名 
GROUP BY 列名2;

--例1：生徒でグループ化、合計点を取得する
select student_code , sum(score) 
from t_student_score 
group by school_code,student_code 
order by sum(score) desc;

--例2:教科でグループ化、合計点を取得する
select subject_code , sum(score) 
from t_student_score 
group by school_code,subject_code 
order by sum(score) desc;

Min…最小値を求める、Max　最大値を求める

Minは最小値を求める関数です。

--Minを使ったSQLの書き方
select Min(列名1) from テーブル名 GROUP BY 列名2;   --最小値を求める
select Max(列名1) from テーブル名 GROUP BY 列名2;   --最大値を求める

--生徒ごとに最低点、最高点を取得する
select student_code , min(score) , max(score) 
From t_student_score 
group by school_code,student_code 
order by student_code;

avg…平均点を求める

avgは最小値を求める関数です。

--avgを使ったSQLの書き方
select avg(列名1) from テーブル名 GROUP BY 列名2;

--生徒ごとに平均点を取得する
select student_code , avg(score) 
From t_student_score 
group by school_code,student_code 
order by student_code;

COUNT…行数（データの数）をカウントする

COUNTは最小値を求める関数です。

--countを使ったSQL文の例
select count(*) from テーブル名 GROUP BY 列名2;

--生徒ごとの点数データ数をカウントする
select student_code,count(*)
From t_student_score 
group by school_code,student_code 
order by student_code;

検索文字位置を取得する

使用例

ちょっとだけ緩く比較する

関連記事（文字列操作）

小文字を大文字に変換する

実行例

注意点

For文（ループ文）の書き方

使用例

ループ文の抜け方

変数の値を画面に出す（raise）

変数の中身が表示された様子

raise動作確認用の関数

関数（FUNCTION）の作り方・書き方を解説する

【①の箇所】関数の名前

【②の箇所】戻り値

【③の箇所】引数

【④の箇所】処理

関数のテンプレート

自作関数の実行の仕方

文字を日付に変換する

文字列を日付に変換する例

文字列 → 日付に変換する例

文字列 → 日付に変換 ＋ 書式設定をする例

数値文字列を「yyyy/mm/dd」形式にする例

数値を日付に変換する

構文

前0埋め

後ろ0埋め

Nullの時に置換する方法

使用例

合計値、平均値を求める例

その他の文字列操作

GROUP BYとは

集約関数の種類

GROUP BYの構文と注意

GROUP BYの注意（並び替え）

SUM…合計を求める

Min…最小値を求める、Max 最大値を求める

avg…平均点を求める

COUNT…行数（データの数）をカウントする

文字列 → 日付に変換＋書式設定をする例

Min…最小値を求める、Max　最大値を求める