PostgreSQL で文字列から UUID 型の列に値を設定するには

とあるアプリケーションを作っていて、とある 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 にでも譲ります。

UUID – Wikipedia

通常は d8557f3c-592f-4f38-8239-c3974dd8d099 のような形式もしくは波括弧がついたりなんかして {a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11} みたいな形式で表されます。

Windows では GUID (こちらは Global Unique IDentifier) と呼ばれることが多いです。

そんな GUID/UUID を格納する専用の列が PostgreSQL には存在していて、それが UUID 型 というわけです。

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 しているようなのでハッシュテーブルが作成された後は、ほとんど変わらない速度で結合できているらしいです。

SNSでもご購読できます。

コメントを残す

*