🐘

PostgreSQLのVALUESをINSERT以外でも使う

2021/12/10に公開

PostgreSQLのVALUESですが、良く使うのはINSERTで複数レコードまとめて追加するときだと思いますが、

INSERT INTO users(id, name) VALUES
  (1, 'Taro'),
  (2, 'Hanako'),
  (3, 'Michael');

INSERTの時だけではなく、SELECTが書けるようなところならば、どこでも書けます。
VALUESは行の集合を表すもので、SELECTと同じ扱いになるからです。

例えばVALUES単独で書くと下記のようになります。

VALUES
  (1, 'Taro'),
  (2, 'Hanako'),
  (3, 'Michael');
testdb=# VALUES
testdb-#   (1, 'Taro'),
testdb-#   (2, 'Hanako'),
testdb-#   (3, 'Michael');
 column1 | column2
---------+---------
       1 | Taro
       2 | Hanako
       3 | Michael
(3 rows)

カラム名はcolumn1 column2 column3 .. といったようにcolumn + 通番が付与されます。

カラム名を付けたい場合には、FROM句で書いてASで付けるような形になります。

SELECT
  *
FROM
  (
    VALUES
      (1, 'Taro'),
      (2, 'Hanako'),
      (3, 'Michael')
  ) AS t (id, name);
testdb=# SELECT
testdb-#   *
testdb-# FROM
testdb-#   (
testdb(#     VALUES
testdb(#       (1, 'Taro'),
testdb(#       (2, 'Hanako'),
testdb(#       (3, 'Michael')
testdb(#   ) AS t (id, name);
 id |  name
----+---------
  1 | Taro
  2 | Hanako
  3 | Michael
(3 rows)

VALUES のINSERT時以外の使い道

定数テーブルとして他のテーブルとJOINする

定数となるような情報を元に集計するような場合に、定数テーブルとしてVALUESを使うと良いかもしれません。

下記のSQLは、salesテーブルのamountを所定の範囲毎に集計するSQLで、所定の範囲をVALUESを使って定数テーブルとして定義しています。

SELECT
  range.amount_from
  , range.amount_to
  , COUNT(sales) AS count
FROM
  (
    VALUES
      (   0,   99),
      ( 100,  999),
      (1000, 9999)
  ) AS range (amount_from, amount_to)
  LEFT JOIN sales
    ON(sales.amount BETWEEN range.amount_from AND range.amount_to)
GROUP BY
  range.amount_from
  , range.amount_to
ORDER BY
  range.amount_from;
testdb=# SELECT
testdb-#   range.amount_from
testdb-#   , range.amount_to
testdb-#   , COUNT(sales) AS count
testdb-# FROM
testdb-#   (
testdb(#     VALUES
testdb(#       (   0,   99),
testdb(#       ( 100,  999),
testdb(#       (1000, 9999)
testdb(#   ) AS range (amount_from, amount_to)
testdb-#   LEFT JOIN sales
testdb-#     ON(sales.amount BETWEEN range.amount_from AND range.amount_to)
testdb-# GROUP BY
testdb-#   range.amount_from
testdb-#   , range.amount_to
testdb-# ORDER BY
testdb-#   range.amount_from;
 amount_from | amount_to | count
-------------+-----------+-------
           0 |        99 |     0
         100 |       999 |     3
        1000 |      9999 |     1
(3 rows)

WITH句と組み合わせてSQLを試す

このSQLはこういう値の時にどうなるか、、ってのをちょっと試したいようなときに、いちいちテーブルにデータ入れたりするのは面倒です。

そういったときにはWITH+VALUESで定数テーブルを作って、それに対してSQLを試行すると便利です。

WITH users(id, name) AS (
  VALUES
    (1, 'Taro'),
    (2, 'ハナコ'),
    (3, 'Michel')  
)
SELECT
  *
  , normalize(name, NFKC)
FROM
  users;
testdb=# WITH users(id, name) AS (
testdb(#   VALUES
testdb(#     (1, 'Taro'),
testdb(#     (2, 'ハナコ'),
testdb(#     (3, 'Michel')
testdb(# )
testdb-# SELECT
testdb-#   *
testdb-#   , normalize(name, NFKC)
testdb-# FROM
testdb-#   users;
 id |     name     | normalize
----+--------------+-----------
  1 | Taro         | Taro
  2 | ハナコ          | ハナコ
  3 | Michel | Michel
(3 rows)

FROM句にVALUESを書いても同じことができるのですが、個人的にはこっちの方がしっくりきます。
対象のデータが先に並んで、それに対してSQLを書くといった形になるのと、履歴から修正する際に下の方から戻るので戻る量が少ないといったところからでしょうか。
※今回のこの記事は、これが書きたくて書いてます。

参考: PostgreSQLのドキュメント

Discussion