🐘
PostgreSQLのVALUESをINSERT以外でも使う
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, 'Ｍｉｃｈｅｌ')
)
SELECT
*
, normalize(name, NFKC)
FROM
users;
testdb=# WITH users(id, name) AS (
testdb(# VALUES
testdb(# (1, 'Taro'),
testdb(# (2, 'ﾊﾅｺ'),
testdb(# (3, 'Ｍｉｃｈｅｌ')
testdb(# )
testdb-# SELECT
testdb-# *
testdb-# , normalize(name, NFKC)
testdb-# FROM
testdb-# users;
id | name | normalize
----+--------------+-----------
1 | Taro | Taro
2 | ﾊﾅｺ | ハナコ
3 | Ｍｉｃｈｅｌ | Michel
(3 rows)
FROM句にVALUESを書いても同じことができるのですが、個人的にはこっちの方がしっくりきます。
対象のデータが先に並んで、それに対してSQLを書くといった形になるのと、履歴から修正する際に下の方から戻るので戻る量が少ないといったところからでしょうか。
※今回のこの記事は、これが書きたくて書いてます。
Discussion