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

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

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

※ 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"