読者です 読者をやめる 読者になる 読者になる

ほんじゃら堂

めんどくさい仕事をラクにする作業自動化レシピ集

PostgreSQLのJSONB型カラム内のキーで一意制約&UPSERT

IT系・技術系 postgresql sql

f:id:piro_suke:20161204023310j:plain

Node.jsとPostgreSQLを使ってるなら、

もうデータは全部JSON形式で管理した方が楽なんじゃないの、

ということでPostgreSQLのJSON型カラムについて色々調べている。

JSON/JSONB型カラムの基本的な使い方についてはちょっと前に書いた。

blog.honjala.net

今回はJSONB型カラムに一意制約をつけたり、

それを利用してUPSERT処理を行う方法について調べたことを書く。

準備

前回書いたUPSERT記事と同じようなことがしたいので、

blog.honjala.net

下記のような商品データ用テーブルを用意する。

id列以外の情報はJSONB型カラムに入れちゃう想定。

drop table if exists upsert_products_js;
create table upsert_products_js(
    id serial
    ,jsdoc jsonb
    ,primary key(id)
);

下記のようなinsert文で商品コード、商品名を登録するものとする:

insert into upsert_products_js(jsdoc)
values(jsonb_build_object(
    'item_cd', '<商品コード>'
    ,'name', '<商品名>'
))
;

下記のようなクエリで確認する:

select 
    jsdoc->>'item_cd' as item_cd
    ,jsdoc->>'name' as name
from upsert_products_js 
order by id
;

jsdoc->>'item_cd' が重複登録されないようにしたい。

JSONB型カラムに一意制約をつける

前回のUPSERT検証により、

ちゃんとUPSERT処理が働くようにするには

キーとしたいカラムに一意制約をつけて

INSERT ON CONFLICTを使うか

INSERT時EXCEPTIONをキャッチするか

のどちらかの方法を取れば良いということが分かったので、

今回一意にしたい jsdoc->>'item_cd' に一意制約をつける。

のだけど、CREATE TABLE文のUNIQUE制約はカラム名しか受け取れないのか、

下記のようにjsonb型カラム内のキーを指定しても構文エラーとなる。

drop table if exists upsert_products_js;
create table upsert_products_js(
    id serial
    ,jsdoc jsonb
    ,primary key(id)
    ,unique (jsdoc->>'item_cd') -- エラーになる
    --,unique ((jsdoc->>'item_cd')) -- こっちの書き方もエラー
);

書き方を間違えてるのかな?

調べてみるとunique制約は裏で一意制約インデックスを作成しているようなので、

create table文とは別で一意インデックスを作成してみる:

drop index if exists idx_upsert_products_js_item_cd;
create unique index idx_upsert_products_js_item_cd on upsert_products_js ((jsdoc->>'item_cd'));

こちらはエラーなく登録される。

JSONB型カラムのキーにUNIQUE制約をつけたい時は

別途一意制約インデックスをつける必要がある、ということかな。

同時UPSERTで検証する

実際に一意制約がちゃんと働いているかを確認するために

UPSERT用関数を作成してマルチスレッド実行してみよう。

INSERT ON CONFLICT検証用関数:

drop function if exists sp_upsert_product_json_on_conflict(varchar(100), varchar(100));
create function sp_upsert_product_json_on_conflict(
    p_item_cd varchar(100)
    ,p_name varchar(100)
) returns void as $$
begin
    insert into upsert_products_js(jsdoc)
    values(jsonb_build_object(
        'item_cd', p_item_cd
        ,'name', p_name
    ))  
    on conflict ((jsdoc->>'item_cd'::text)) do update
--    set jsdoc = jsdoc || jsonb_build_object( -- こちらの書き方だとjsdocが曖昧だというエラーになる
--            'name', p_name
--        )   
    set jsdoc = upsert_products_js.jsdoc || jsonb_build_object(
            'name', p_name
        )   
    ;   
end;
$$ language plpgsql;

UNIQUE_VIOLATION EXCEPTION検証用関数:

drop function if exists sp_upsert_product_json_on_exception(varchar(100), varchar(100));
create function sp_upsert_product_json_on_exception(
    p_item_cd varchar(100)
    ,p_name varchar(100)
) returns void as $$
begin
    insert into upsert_products_js(jsdoc)
    values(jsonb_build_object(
        'item_cd', p_item_cd
        ,'name', p_name
    ))  
    ;   
exception when unique_violation then
    update upsert_products_js
    set 
        jsdoc = jsdoc || jsonb_build_object(
            'name', p_name
        )   
    where
        jsdoc->>'item_cd' = p_item_cd
    ;   
end;
$$ language plpgsql;

前回のUPSERT記事と同様にそれぞれの関数を2つのスレッドで

同時に実行し、jsdoc->>'item_cd' が重複せず登録できるか確認したところ、

どちらも期待通り登録できた。

おわり

JSONB型データのキーで一意制約を設定できることが分かって一安心。

通常のカラムと書き方がちがうことがあるので試行錯誤が必要だけど、

だいたい同じように扱えるように作ってくれてるようだ。

PostgreSQL徹底入門 第3版

PostgreSQL徹底入門 第3版

  • 作者: 笠原辰仁,北川俊広,坂井潔,坂本昌彦,佐藤友章,石井達夫
  • 出版社/メーカー: 翔泳社
  • 発売日: 2011/02/02
  • メディア: 大型本
  • 購入: 3人 クリック: 28回
  • この商品を含むブログ (6件) を見る