Help us understand the problem. What is going on with this article?

PostgreSQL で id 配列の登場順に従って連番を割り当てる UPDATE 文を書きたい

More than 3 years have passed since last update.

データ準備

以下の様なテーブルを用意。

=# CREATE TABLE items (id SERIAL, value TEXT, sequence INTEGER);
CREATE TABLE
Time: 194.164 ms

初期データは以下の通り。

=# INSERT INTO items (value, sequence) VALUES ('A', 1), ('B', 2), ('C', 3), ('D', 4);
INSERT 0 4
Time: 8.454 ms
=# SELECT * FROM items;
 id | value | sequence
----+-------+----------
  1 | A     |        1
  2 | B     |        2
  3 | C     |        3
  4 | D     |        4
(4 rows)

Time: 12.149 ms

問:こういう感じ

id の配列 [2, 3, 1, 4] が与えられた時に、1 つの UPDATE 文で以下の結果を実現したい。

 id | value | sequence
----+-------+----------
  2 | B     |        1
  3 | C     |        2
  1 | A     |        3
  4 | D     |        4

案1:VALUES を使った場合

例えば、以下の様に VALUES を使って id と順番のペアからなるテーブルを組み立てる事ができる。

=# SELECT * FROM (VALUES (2, 1), (3, 2), (1, 3), (4, 4)) AS t (id, new_sequence);
 id | new_sequence
----+--------------
  2 |            1
  3 |            2
  1 |            3
  4 |            4
(4 rows)

Time: 0.361 ms

これを利用して UPDATE を書いてみる。

=# UPDATE items SET sequence = t.new_sequence
-#  FROM (VALUES (2, 1), (3, 2), (1, 3), (4, 4)) AS t (id, new_sequence)
-# WHERE items.id = t.id;
UPDATE 4
Time: 15.526 ms
=# SELECT * FROM items ORDER BY sequence;
 id | value | sequence
----+-------+----------
  2 | B     |        1
  3 | C     |        2
  1 | A     |        3
  4 | D     |        4
(4 rows)

Time: 11.036 ms

案2:JSON を使う

JSON で対応表を作って使ってみる。

=# UPDATE items SET sequence = ('{"2":"1","3":"2","1":"3","4":"4"}'::json->>id::TEXT)::INTEGER;
UPDATE 4
Time: 8.656 ms
=# SELECT * FROM items ORDER BY sequence;
 id | value | sequence
----+-------+----------
  2 | B     |        1
  3 | C     |        2
  1 | A     |        3
  4 | D     |        4
(4 rows)

Time: 0.411 ms

案3:array_position を使う(9.5+)

9.5 から array_position という関数が使えるので使ってみる。

=# UPDATE items SET sequence = array_position(ARRAY[2, 3, 1, 4], id);
UPDATE 4
Time: 8.854 ms
=# SELECT * FROM items ORDER BY sequence;
 id | value | sequence
----+-------+----------
  2 | B     |        1
  3 | C     |        2
  1 | A     |        3
  4 | D     |        4
(4 rows)

Time: 0.498 ms

没1:ROW_NUMBER と ORDER BY FIELD

MySQL の ORDER BY FIELD 相当の機能が使えれば ROW_NUMBER() OVER (ORDER BY FIELD(id, [2, 3, 1, 4])) な感じでいけるのではないかと妄想した。

=# SELECT ROW_NUMBER() OVER (ORDER BY id DESC), * FROM items;
 row_number | id | value | sequence
------------+----+-------+----------
          1 |  4 | D     |        4
          2 |  3 | C     |        3
          3 |  2 | B     |        2
          4 |  1 | A     |        1
(4 rows)

Time: 0.379 ms
koshigoe
feedforce
『「働く」を豊かにする。』というミッションを掲げ、企業向けネットサービスを開発・提供しています。
https://www.feedforce.jp
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away