構文

--前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"

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)

 

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;

指定した文字の出現回数をカウントする

文字列から指定した文字の出現回数をカウントする

 

 

引数

引数1(character varying):対象の文字列

引数2(character varying):探す文字列

 

 

返り値

文字の出現回数(数値)

 

 

コード

CREATE OR REPLACE FUNCTION count_appearances(
    character varying,
    character varying)
  RETURNS integer AS
$BODY$
declare
	c_target_char	alias for $1;	--引数1:対象の文字列
	c_seach_char	alias for $2;	--引数2:探す文字列
begin

	return (length(c_target_char) - length(replace(c_target_char, c_seach_char, ''))) / length(c_seach_char);

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

 

 

実行例

--文字列'AAbbccddefghiA'から'A'の個数をカウントする
select * From count_appearances('AAbbccddefghiA','A');  --結果:3

--文字列'abcdefabcdef'から'abc'の個数をカウントする
select * From count_appearances('abcdefabcdef','abc');  --結果:2

 

次の月曜日、火曜日、○曜日を求める(next_day関数)

基準となる日付より後で、最初の指定した曜日の日付を取得します。

 

引数

引数1(date):基準となる日付

引数2(integer):取得したい曜日(0:日、1:月、2:火、3:水、4:木、5:金、6:土)

 

返り値

基準となる日付の翌日(当日は含まない)以後の日で、指定した曜日の日付

 

コード

CREATE OR REPLACE FUNCTION next_day(
    date,
    integer)
  RETURNS date AS
$BODY$
declare

	d_target	alias for $1;  --引数1:対象の日付
	i_weekday	alias for $2;  --引数2:取得する曜日(0:日 , 1:月 , 2:火 , 3:水 , 4:木 , 5:金 , 6:土)

	d_temp		date;
	
BEGIN

	for temp_count in 1..7
 	loop
    --日付を1日ずつ加算し、i_weekdayと一致したらリターンする
		d_temp = d_target + cast(cast(temp_count as character varying) || ' days' as INTERVAL);
		if date_part('dow', d_temp) = i_weekday then
		return d_temp;
		end if;
	end loop;
	

	return d_temp;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION next_day(date, integer)
  OWNER TO postgres;

※PostgreSQL9.5 , 9.6 , 10 , 11 , 12で動作確認済み 自作関数の実行方法はこちら

 

実行例

select * from next_day('2019/9/23',2);
--2019/9/24(2019/9/23より後の最初の火曜日)

select * from next_day('2019/9/23',0);
--2019/9/29(2019/9/23より後の最初の日曜日)

 

PostgreSQLの構文まとめを書いています

入門~経験者まで「基本構文・こんな時どう書くんだっけ?」のまとめ

pgAdminでデータベースのバックアップを取る方法

このページでは、DBのバックアップの方法を紹介します。

バックアップを取っておくと、何かの時にその時点まで戻せるようになります。

定期的にバックアップする癖をつけ、万が一の時に備えるようにしておくとよいと思います。

 

pgAdminⅢでのバックアップ

1.バックアップを取得するDBを選択し、右クリックから「バックアップ(B)」を選択します。

2.赤枠をクリックします。

 

3.①の箇所にファイル名を入力します。名前は何でも大丈夫です。(拡張子は.backupになります。)ファイル名を入力したら②の保存を押します。

 

4.赤枠の「バックアップ(B)」を押すと、バックアップが開始されます。

 

5.少し待つと文字が画面のように文字が表示されますので、「完了」を押して下さい。

6.以上でバックアップが完了です。

説明

OracleのDecode関数まがいのものです。

条件に合致した場合は指定した値が返ります。合致しない場合、最後の引数の値が返ります。

 

引数

引数1(character varying):チェックする値

引数2(character varying[]):条件値(配列)

引数3(character varying[]):条件値に一致する場合の返り値(配列)

引数4(character varying):条件に一致しない場合の返り値

 

返り値

引数3の値、(合致しない場合)引数4の値

 

コード

CREATE OR REPLACE FUNCTION public.decode(
	c_target character varying,
	a_conditions character varying[],
	a_return character varying[],
	c_else_return character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    
AS $BODY$
declare
/*
    引数1:チェックする値
    引数2:条件値
    引数3:条件値に一致する場合の返り値
    引数4:条件に一致しない場合の返り値
*/
BEGIN

	--配列の分だけループ
	for i in 1..array_length(a_conditions,1) loop
		if c_target = a_conditions[i] then
		    return a_return[i];
		end if;
	end loop;
	
	--条件に一致しない時、4つめの引数でリターン
	return c_else_return;

END;
$BODY$;

ALTER FUNCTION public.decode(character varying, character varying[], character varying[], character varying)
    OWNER TO postgres;

 

実行例

select decode('S'  , array['S','M','L'] , array['小','中','大'] ,'該当なし');   --小
select decode('M'  , array['S','M','L'] , array['小','中','大'] ,'該当なし');   --中
select decode('L'  , array['S','M','L'] , array['小','中','大'] ,'該当なし');   --大
select decode('XL' , array['S','M','L'] , array['小','中','大'] ,'該当なし');   --該当なし

 

PostgreSQLの構文まとめを書いています

入門~経験者まで「基本構文・こんな時どう書くんだっけ?」のまとめ

文字を左から○文字切り取る(Left関数)

文字列の左から○文字取得するには、Left関数を使用します。

切り取る文字数で指定するのは、全半角を考慮しない単純な文字数です。

--Left関数
Left( 切り取る文字列 , 左から切り取る文字数 )

--文字列'ABCDEFGHIJK'について、左から3文字切り取る
select * from left('ABCDEFGHIJK',3);  -- "ABC"

--文字列'あいうえおかきくけこ'について、左から5文字切り取る
select * from left('あいうえおかきくけこ',5);  -- "あいうえお"

 

 

文字を右から○文字切り取る(Right関数)

文字列の右から○文字取得するには、Right関数を使用します。

切り取る文字数で指定するのは、全半角を考慮しない単純な文字数です。

--Right関数
Right( 切り取る文字列 , 右から切り取る文字数 )

--文字列'ABCDEFGHIJK'について、右から3文字切り取る
select * from right('ABCDEFGHIJK',3);  -- "IJK"

--文字列'あいうえおかきくけこ'について、右から5文字切り取る
select * from right('あいうえおかきくけこ',5);  -- "かきくけこ"

 

 

文字を左のX文字目から○文字切り取る(Substring関数)

文字列から指定した部分を取得するには、substring関数を使用します。

切り取る位置、文字数で指定するのは、全半角を考慮しない単純な文字数です。

--Substring関数
--「切り取り始める位置」は、0からではなく1からカウント
Substring( 切り取る文字列 , 切り取り始める位置 , 切り取る文字数)

--文字列'ABCDEFGHIJK'について、3文字目から4文字切り取る
select * from substring('ABCDEFGHIJK',3,4);  --"CDEF"

--文字列'あいうえおかきくけこ'について、5文字目から3文字切り取る
select * from substring('あいうえおかきくけこ',5,3);  --"おかき"

--3つ目の引数『切り取る文字数』は、その文字列の文字数を超えてもエラーにはなりません
--次の例は3文字目から100文字取得としていますが、エラーにならず最後まで取得可能です
select * from substring('ABCDEFGHIJK',3,100);  --"CDEFGHIJK"

 

 

先頭から○文字削る、後ろから○文字削る

substringとlengthを使い、文字を削る例を紹介します。

-- 1.先頭からX文字を削る例
--'abcdefgh'の先頭3文字を削る(=4文字目以降すべて取得する)
select * from substring('abcdefgh',4,length('abcdefgh'));    --"defgh"

-- 2.後ろからX文字を削る例
--'abcdefgh'の後ろ3文字を削る
select * from substring('abcdefgh',1,length('abcdefgh')-3);  --"abcde"

 

あわせて読みたい

https://postgresweb.com/post-5440

 

Where文

Where文をSelect文の後につけることで、抽出条件を指定できます。

select 列名 from テーブル名 where 条件;

抽出条件が複数あった場合は、「and」でつなぎます。

select 列名 from テーブル名 where 条件1 and 条件2;

 

Select文に抽出条件を加えた例です。

--学生コード = 'stu01'となっているデータを抽出する。
select * from m_student_score where student_code = 'stu01';

--得点(score)が80以上のデータを取得する
select * from m_student_score where score >= 80;

--得点(score)が50以下のデータを取得する
select * from m_student_score where score <= 50;

--得点(score)が50以下のデータ 
--かつ 学生コードが'sub06'となっているデータを抽出する。
select * from m_student_score where score <= 50 and student_code = 'stu06'; 

--得点(score)が30以上50以下のデータを取得する(以下の二つは結果が同じです)。 
select * from m_student_score where score >= 30 and score <= 50;
select * from m_student_score where score between 30 and 50;

 

like

文字を抽出条件にする時、等しい場合は「=」、部分一致の場合は「like」を使います。

--等しいものを抽出
select 列名 from テーブル名 where 条件 = 文字列;

--前方一致するものを抽出
select 列名 from テーブル名 where 条件 like 文字列%;

--後方一致するものを抽出
select 列名 from テーブル名 where 条件 like %文字列;

--部分一致するものを抽出
select 列名 from テーブル名 where 条件 like %文字列%;

 

like例

--名前が「織田信長」と一致するデータを取得
select * From m_student where student_name = '織田信長';

--名前が「豊臣」から始まるデータを取得
select * From m_student where student_name like '豊臣%';

--名前が「家康」で終わるデータを取得
select * From m_student where student_name like '%家康';

--名前が「田」を含むデータを取得
select * From m_student where student_name like '%田%';

 

In

「○か○か○のもの」のように条件を指定する場合は、IN句を使用します。

select 列名 from テーブル名 where 列名 in(値1 , 値2 , 値3);

--例:商品分類が食品、電化製品、車の商品を取得する。
select * from 商品 where 分類 in('食品' , '電化製品' , '車');

 

Not In

「○か○か○でないもの」のように条件を指定する場合は、NOT IN句を使用します。

select 列名 from テーブル名 where 列名 not in(値1 , 値2 , 値3);

--例:商品分類が食品、電化製品、車でない商品を取得する。
select * from 商品 where 分類 not in('食品' , '電化製品' , '車');

 

サブクエリ

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

select 列名 from テーブル名1 
where 列名1 in(select 列名2 from テーブル名2); --列名1と列名2は一致してなくてもOKです。

--例:Aさんが購入した商品を取得する
select * from 商品 where 商品コード in(select 商品コード from 商品購入履歴 where ユーザーID = AさんのID);

上の例では、()内の『select 商品コード from 商品購入履歴 where ユーザーID = AさんのID』の

結果で、商品コードで「商品」テーブルを取得しています。

構文

数値を文字に変換し書式を設定するには、to_char(数値 ,  書式)を使用します。

使用例はこの下を参照して下さい。

--数値を文字に変換する
to_char( 数値 , 書式 )

 

あわせて読みたい

※ 書式の指定が必要ない数値→文字の変換は、castを使う方法がおすすめです

https://postgresweb.com/post-342

 

 

使用例

to_charを使うと先頭に謎の空白が入るので、「FM」をつけ、空白がつかないようにしています。

0と9の違いは、0は必ずその桁数まで表示され、9は数値がある桁数まで表示します。

書式は公式に例がありますが実務では使用しないものが多く、次の例で事足ります。

-- カンマ区切り+少数点(3桁まで表示)
select * From to_char(12345.67, 'FM999,999.999');     -- 12,345.67

-- カンマ区切り+少数点(3桁で表示)
select * From to_char(12345.67, 'FM999,999.000');     -- 12,345.670

-- マイナス値+カンマ区切り+少数点(3桁まで表示)
select * From to_char(-12345.67, 'FM999,999.999');     -- -12,345.67

-- マイナス値+カンマ区切り+少数点(3桁で表示)
select * From to_char(-12345.67, 'FM999,999.000');     -- -12,345.670

 

 

返り値の最後が「.」になってしまう時の対処方法

引数の値が整数なのか小数なのかはっきりしない場合、

例1のような書式設定をすると、返り値の最後が「.」で終わってしまうケースがあります。

その場合、例2のようにcase文を使って場合分けをすると解決できます。

 -- 例1.返り値の最後が「.」になってしまうケース
select to_char(12345, 'FM999,999.999');   -- "12,345." 

-- 例2.帰り値の最後が「.」になってしまうのを対処したパターン(CASE文を使用) 
select case 
when right(to_char(12345, 'FM999,999.9'),1) = '.' then 
to_char(12345, 'FM999,999')              -- 最後が「.」なら整数として書式設定する
else to_char(12345, 'FM999,999.9') end;  -- そうでなければ小数用の書式設定をする
-- "12,345"

 

 

to_charを使った日付書式

to_charは日付の書式設定にも使用することができます。

 

あわせて読みたい

https://postgresweb.com/post-399