BANKURA WIKI Wiki*

日時の時間切り捨て
年度末時点の年齢の算出
文字列の切り取り
NULLの比較
その他メモ

日時の時間切り捨て

PostgreSQLでの日時項目の時間部分切り捨ては、date_trunc関数を使用する。

date_trunc('day', 日時項目)

年度末時点の年齢の算出

PostgreSQLで4/1生まれを3/31生まれとみなす「年齢計算ニ関スル法律」を満たす年度末時点の年齢を生年月日から算出するには、以下のようにする。

extract(year from age((extract(year from (current_date + '-3 months'::interval + '1 years'::interval)) || '-03-31')::date, 生年月日 - 1))

文字列の切り取り

PostgreSQLで文字列の一部分を切り取るには、substr関数を使用する。

substr(文字項目, 開始位置, 文字数)

開始位置は1始まり。

NULLの比較

SQLの条件式（=, <>, <, >, <=, >=での比較）の左辺か右辺にnull値が入る場合、比較結果が「true」とも「false」とも違う「unknown」となり、レコードが抽出されない。
nullは何と比較してもunknownを返すため、is null や is not null でないと比較できない。

select [id] from [test] where
  not ([name1] = [name2])

上記の場合、name1かname2のどちらかもしくは両方にnullが入ると、([name1] = [name2]) は「false」ではなく「unknown」となり、not unknownの結果も「unknown」となり、レコードが抽出されない。
そのため、以下のようにis null や is not nullを組み合わせた条件式で対応する必要がある。

select [id] from [test] where
  [name1] <> [name2]
  or ([name1] is null and [name2] is not null)
  or ([name1] is not null and [name2] is null)

3値論理

上記の問題は、データベースの大元の設計思想が3値論理に基づくために発生する。
3値論理は、「true」「false」の2値に「unknown」を加えた論理。

「null = null」はunknownと評価される。
nullの中の値が何なのか分からないという風に解釈すると、当然NULL同士の比較は分からないもの同士の比較なので、結果としても分からないため、unknownと評価される。

IS [NOT] DISTINCT FROM 演算子

PostgreSQLでは、「IS [NOT] DISTINCT FROM」演算子を使用することで、nullを含む条件の比較がunknownとならないようにできる。

A	B	A = B	A IS NOT DISTINCT FROM B
0	0	true	true
0	1	false	false
0	null	unknown	false
null	null	unknown	true

A	B	A <> B	A IS DISTINCT FROM B
0	0	false	false
0	1	true	true
0	null	unknown	true
null	null	unknown	false

標準SQL(SQL99 以降) の構文のため、今後他のDBMSにも取り入れられる可能性がある。

参考リンク

その他メモ

なにかあれば。

IT系/misc/SQLメモ