4.2 演算子と関数

PostgreSQLでサポートされている主な演算子と関数について説明します。演算子と関数を用いることにより、計算結果を出力する値式や、真偽を判定する論理式を構成することができます。また、SQL特有の構文で論理式と同様の判定を行う述語も存在します。

論理演算子

論理演算子は、AND(論理積)、OR(論理和)、NOT(否定)の3種類で、真理値には、true(真)、false(偽)、null(不明)の3種類があります。真理値表を以下に示します。

x y x AND y x OR y NOT x
true true true true false
true false false true
true null null true
false true false true true
false false false false
false null false null
null true null true null
null false false null
null null null null

nullを含む3値の論理になっていることに注意してください。

比較演算子

比較演算子は、以下の6種類があり、2つの値の大小関係を判定します。

演算子 = < <= > >= <>, !=
機能 等しい 小さい 以下 大きい 以上 等しくない

!= は標準SQLにはありません。

比較演算子は入力値がNULL(空値)の場合、比較結果はnull(不明)になります。入力値がNULLかどうかを判定するために、次に説明する述語が用意されています。

比較述語

比較述語には、BETWEENとIS NULLがあります。

BETWEEN述語は、値式が下限値と上限値で指定された範囲内にあるかどうかを判定します。範囲には下限値と上限値の両方が含まれます。形式は以下の通りです。

値式 [NOT] BETWEEN 下限値 AND 上限値
  • NOTを指定すると範囲外の場合に真になります。
  • 下限値と上限値は、任意の値式です。

例えば「price BETWEEN 1000 AND 1999」と指定すると、price列の値が1000以上かつ1999以下の場合に真になります。

IS NULL述語は、値式がnullかどうかを判定します。形式は以下の通りです。

値式 IS [NOT] NULL
  • NOTを指定するとnullでない場合に真になります。

例えば「price IS NULL」と指定すると、price列の値がnullの場合に真になります。

算術演算子と関数

算術演算子には、以下のものがあります。

演算子 + - * / % ^
機能 加算 減算 乗算 除算 剰余 累乗

マイナス記号(-)は正負反転の単項演算子としても使用できます。

算術関数には、多くのものがあります。代表的な関数を以下に示します。

関数名 機能 使用例
abs 絶対値 abs(-17.4) → 17.4
ceil, ceilling 整数に切り上げ ceil(42.2) → 43
div 整数商 div(9,4) → 2
floor 整数に切り下げ floor(42.8) → 42
mod 剰余 mod(9, 4) → 1
power 累乗 power(9, 3) → 729
round 最も近い整数に丸め round(42.4) → 42
sqrt 平方根 sqrt(2) → 1.4142135623730951

この他に、乱数関数、三角関数、双曲線関数があります。

文字列演算子と関数

文字列演算子には、以下のものがあります。

演算子 ||
機能 文字列の連結


文字列関数にも、多くのものがあります。代表的な関数を以下に示します。

関数名 機能 使用例
char_length 文字数 char_length('josé') → 4
concat 文字列に変換して連結 concat('abcde', 22) → abcde22
lower 小文字に変換 lower('TOM') → tom
octet_length 文字列のバイト数 octet_length('josé') → 5 (UTF8)
position 文字列開始位置 position('om' in 'Thomas') → 3 (左端が1)
substring 部分文字列 substring('Thomas' from 2 for 3) → hom (左端が1)
trim 前後の文字を削除 trim(both 'xyz' from 'yxTomxx') → Tom
upper 大文字に変換 upper('tom') → TOM

文字列述語

文字列を扱う述語にはLIKEとSIMILAR TOがあります。

LIKE述語は、値式の文字列がパターンと一致するかどうかを判定します。形式は以下の通りです。

値式 [NOT] LIKE パターン
  • NOTを指定するとパターンと一致しない場合に真になります。
  • パターンは任意の文字列です。パターン中にあるアンダースコア(_)は任意の一文字との一致を意味し、パーセント記号(%)は0文字以上の並びとの一致を意味します。

例えば、prod_name列の値が「りんご」の場合、以下の述語が真になります。

  • prod_name LIKE 'りんご'
  • prod_name LIKE 'りん_'
  • prod_name LIKE '_ん_'
  • prod_name LIKE 'り%'
  • prod_name LIKE 'り%ご'
  • prod_name NOT LIKE '%ん'

SIMILAR TO述語は、値式の文字列が正規表現パターンと一致するかどうかを判定します。形式は以下の通りです。

値式 [NOT] SIMILAR TO パターン
  • NOTを指定するとパターンと一致しない場合に真になります。
  • パターンは任意の文字列です。アンダースコア(_)とパーセント記号(%)はLIKE述語と同じで、さらに正規表現も使用できます。

例えば、prod_name列の値が「りんご」の場合、以下の述語が真になります。

  • prod_name SIMILAR TO '(み|り)%'
  • prod_name SIMILAR TO 'りんご(あめ)?'
  • prod_name NOT SIMILAR TO '_{4}'

日付・時刻演算子と関数

日付・時刻の入出力は、ISO 8601形式がデフォルトです。すなわち、年月日時分秒+時間帯の形式で、例えば、'2024-06-19 12:34:56.654321+09' のように表します。なお、この入出力形式はSETコマンドで変更可能です。

日付・時刻演算子には、以下のものがあります。

演算子 + -
機能 加算 減算

演算結果は対象のデータ型によって、整数型、日付型、時刻型、タイムスタンプ型、時間間隔型のいずれかになります。例えば、以下のような演算が可能です。

  • date '2001-09-28' + 7 → 2001-10-05
  • date '2001-10-01' - date '2001-09-28' → 3
  • date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00
  • time '05:00' - time '03:00' → 02:00:00
  • timestamp '2001-09-28 01:00' + interval '23 hours' → 2001-09-29 00:00:00

日付・時刻関数にも、多くのものがあります。代表的な関数を以下に示します。

関数名 機能 使用例
current_date 現在の日付 current_date → 2019-12-23
current_time 現在の時刻 current_time → 14:39:53.662522
current_timestamp 現在の日付と時刻 current_timestamp → 2019-12-23 14:39:53.662522
extract 日付と時刻の一部を抽出 extract(hour from timestamp '2001-02-16 20:38:40') → 20
make_date 日付の生成 make_date(2013, 7, 15) → 2013-07-15
make_time 時刻の生成 make_time(8, 15, 23.5) → 08:15:23.5
make_timestamp 日付と時刻の生成 make_timestamp(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5
now 現在の日付と時刻 now() → 2019-12-23 14:39:53.662522

now関数はSQL非標準です。

集約関数

集約関数は入力値の集合から単一の結果を出力します。これまで説明してきた一般の関数との違いは以下の通りです。

  • 一般の関数は行内の入力値から１行ごとに結果を出力するのに対し、集約関数は表内のすべての行を入力して単一の結果を出力します。ただし、表がグループ化されている場合は、グループごとに結果を出力します。
  • 一般の関数は入力値にNULLが含まれていると、基本的に結果はNULLを出力するのに対し、集約関数はNULL値を除外して計算した結果を出力します。ただし、入力値がすべてNULLの場合はNULLを出力します。

代表的な集約関数を以下に示します。

関数名 機能 指定方法
count 行数(NULL値を含む) count(*)
count 行数(NULL値を除く) count([distinct] 値式)
sum 合計値(NULL値を除く) sum([distinct] 値式)
avg 平均値(NULL値を除く) avg([distinct] 値式)
max 最大値(NULL値を除く) max(値式)
min 最小値(NULL値を除く) min(値式)

distinctを指定すると、重複した値を除外して計算を行います。

具体例として、count関数を使ったprod表に対するSELECT文を以下に示します。

SELECT COUNT(*), COUNT(prod_id), COUNT(DISTINCT prod_id)
  FROM orders;

COUNT(*)は表全体の行数、COUNT(prod_id)はprod_id列の重複を含む個数、COUNT(DISTINCT prod_id)はprod_id列の重複を除いた個数を出力します。

次の例は、グループ化した表で5種類の集約関数を使った例です。

SELECT prod_id, COUNT(qty), MIN(qty), MAX(qty), SUM(qty), AVG(qty)
  FROM orders
  GROUP BY prod_id;

prod_idごとにqty列の個数、最小値、最大値、合計値、平均値が出力されます。