PostgreSQL で文字列から UUID 型の列に値を設定するには
こんにちは、kenzauros です。
とあるアプリケーションを作っていて、とある PostgreSQL のテーブルで、とある文字列型のカラムに入っている GUID の値を PostgreSQL の UUID 型のカラムに変換して入れたくなったので、そのときのメモです。
調べてもほとんどひっかからなかったので、そもそもそんな変なことをする人は少ないらしいですが、奇特な方のために一応残しておきます。
UUID 型の列とは
この記事を探してこられた方には蛇足だと思いますが、念のため。
UUID とは Universally Unique IDentifier の略で、簡単に言えば「ほぼ世界で一つだけの ID」です。
ビット長が 128 ビットで 340,282,366,920,938,463,463,374,607,431,768,211,456 通り (なんて読むの？) の数が表現できるため、「まぁ、ほぼかぶらないっしょ」というわけです。
本筋ではないので、詳細は Wikipedia にでも譲ります。
通常は d8557f3c-592f-4f38-8239-c3974dd8d099 のような形式もしくは波括弧がついたりなんかして {a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11} みたいな形式で表されます。
Windows では GUID (こちらは Global Unique IDentifier) と呼ばれることが多いです。
そんな GUID/UUID を格納する専用の列が PostgreSQL には存在していて、それが UUID 型 というわけです。
PostgreSQL の内部実装までわかりませんが、たぶん 16 バイトの整数で格納してくれていることでしょう。
ちなみに UUID 型の列定義は難しいこともなく、たとえば guid_column という列を定義する場合は "guid_column" uuid と書くだけです。
文字列を UUID に変換して値を入れる
今回は下記のように文字列型の a 列と UUID 型の b 列があり、 a 列にはすでに d8557f3c-592f-4f38-8239-c3974dd8d099 形式の GUID 文字列が含まれていることとします。
CREATE TABLE public."sample"
(
"a" text, -- すでに GUID が文字列として入った列
"b" uuid -- これから a を変換した値を入れたい列
)b 列に a 列の GUID を入れたいとき、結論からいうと下記のように書くとうまくはまりました。
UPDATE public."sample" SET "b" = uuid('{' || "a" || '}');ミソは uuid() 関数と波括弧 { } です。
(|| は PostgreSQL の文字列結合演算子です。 OR 演算子ではありません)
この uuid() 関数が文字列を UUID 型の値に変換してくれるのですが、これが公式ページにもどこにも載っていないので、行き着くのに時間がかかりました。（ポスグレユーザーには当たり前すぎたのかもしれません）
あと、公式ページにも下記のように書いてあり、いくらかの形式を受け付けてくれるようなのですが、 d8557f3c-592f-4f38-8239-c3974dd8d099 といった形式ではうまくいきませんでした。
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}数字の区切りを見る限り、2 番めの例に一番近いようなので、「それなら波括弧か？！」と思って、 '{' || "a" || '}' のように波括弧を結合してみたら、これが当たりました。
少々泥臭いですが、機嫌よく読み込んでくれる形に整形してあげてください。
JOIN は速くならず。
そもそもなんでこんなことをしようかと思ったかというと、大量にレコードの含まれたテーブル同士を UUID で JOIN していたのですが、件数が増えるごとに速度低下が顕著になりました。
インデックスはってもだめだったので、文字列で UUID を格納しているから遅いのかと思い、試しに今回 UUID 型に変換してみたのです。
実際に UUID 型に変換してそれらの列で結合してみましたが、結果としては JOIN の速度に有意な差は見られませんでした。
はい、ということで、↑ で変換した GUID は結局無駄に終わりました(笑)
EXPLAIN で解析してみるといずれにしろ hash join しているようなのでハッシュテーブルが作成された後は、ほとんど変わらない速度で結合できているらしいです。
