【PostgreSQL】親子関係のあるレコードの serial を uuid に付け替える方法

親子関係のあるレコードをもつテーブル

例えば、下記のようなテーブル(テーブル名=message_threads)を想定します。メールや掲示板のスレッドのようなものをイメージすればわかりやすいと思います。

メッセージID(serial型:mes_id) 返信先メッセージID(serial型:parent_mes_id) メッセージタイトル(text型:mes_title)
1 0 ほげほげ
2 1 Re:ほげほげ その1
3 1 Re:ほげほげ その2
4 2 Re:Re:ほげほげ その1
5 0 ふがふが

※ 0 はルートメッセージを指すものとする。
このメッセージIDと返信先メッセージIDを、serial型からuuid型に変換したいと思います。

スポンサーリンク

考え方

  • メッセージID=1のレコードに対して uuid が決まらないと、返信先メッセージID=1 のレコードの uuid が決まらないため、まずメッセージIDに対して uuid を決める。
  • メッセージIDに対して振られた uuid で、返信先メッセージIDに対して振るべき uuid が決まる。
  • 返信先メッセージID=0 は、ルートメッセージであるため、uuid は振らなくてよい。

SQL(Insert-Select)

uuid値は uuid_generate_v4() 関数を使って採番することができます。この関数がエラーになった場合は、補足の項を参考にしてください。

with message_threads_uuid as (
  -- すべてのレコードに uuid を付与
  select *
       , uuid_generate_v4() as message_uuid
    from message_threads
), message_threads_with_parent_uuid as (
  -- メッセージIDと返信先メッセージIDを紐づけて、返信先メッセージの uuid を決定
  select child.mes_id
       , child.message_uuid as my_uuid
       , parent.message_uuid as parent_uuid
    from message_threads_uuid as child
       , message_threads_uuid as parent
   where child.parent_mes_id = parent.mes_id
), message_threads_temp as (
  -- 返信先メッセージIDが紐づかないレコードには、uuid を振らない(= uuid_nil() とする)
  select main.message_uuid
       , coalesce(sub.parent_uuid, uuid_nil()) parent_message_uuid
       , main.mes_title
    from message_threads_uuid as main
    left join message_threads_with_parent_uuid as sub
           on main.mes_id = sub.mes_id
)
insert into message_threads_conv
  select message_uuid
       , parent_message_uuid
       , mes_title 
    from message_thread_temp

補足:function uuid_generate_v4() does not exist

「function uuid_generate_v4() does not exist」というエラーが発生した場合、下記のSQLを実行することで UUID関連の関数を使用できるようにしてください。

create extension if not exists "uuid-ossp"
スポンサーリンク
関連キーワード
DB, PostgreSQLの関連記事
  • 【PostgreSQL】親子関係のあるレコードの serial を uuid に付け替える方法
  • 【PostgreSQL】Select結果でUpdateする3パターン
おすすめの記事