PostgreSQLで「連番に穴が空いているから詰めて」という依頼に応える方法

導入

今回の話題は業務システムではよくある「連番に穴が空いているから詰めてほしい」という依頼にSQLで応える方法です。

※ 私が株式会社愛宕 Advent Calendar 2023に書く記事は主に社内向けに共有しておきたいけど勉強会をするまでもないちょっとしたTipsにしたいと思います。

例えば、itemsテーブルのid=1〜99の次が150〜180となっているときにid=150〜180を100〜130に移動させることで連番に穴がないようにしたいとします。 普通に移動するなら、

UPDATE items SET id = id - 50;

でOKですが、items.idに外部キー制約がついているとこれができません。
こんな場合にどうするかという問題です。

解決

移動したいデータだけが含まれたテンポラリテーブルを作って対応します。
テンポラリテーブルには外部キー制約がついていないので自由にidを更新できます。

CREATE TEMPORARY TABLE tmp_items as SELECT * FROM items where id between 150 and 180;
UPDATE tmp_items SET id = id - 50;
INSERT INTO items SELECT * FROM tmp_items;
-- ここで制約がついてるテーブルの外部キー更新
DELETE FROM items WHERE id between 150 and 180;