PostgreSQLのJSONB型カラム内のキーにユニーク制約をつけてUPSERTする方法

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;