PostgreSQL

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