JSONB のキーと他のキーの複合キー（by PostgreSQL）

先日書いた
JSONB の primary key を作る ( by PosetgreSQL ) - Oboe吹きプログラマの黙示録
これでは、目的の主キーとして、任意の列とJSONB型のJSON内の値のキーとする場合の
CONSTRAINT を２つ書けない制約でUPSERT文 (ON CONFLICT～DO UPDATE）を思うように書けない。
そこで、PRIMARY KEY 制約で指定するのではなく、
NOT NULL制約と、UNIQUE インデックス制約でON CONFLICT～DO UPDATE を書けるようにする。

DROP TABLE jsample;

CREATE TABLE jsample (
    id INTEGER NOT NULL,
    title VARCHAR(60) NOT NULL,
    jdoc jsonb NOT NULL,
    CONSTRAINT jsample_jdockey_not_null CHECK ((((jdoc->'item'::text)->>'id'::text) IS NOT NULL))
);
CREATE UNIQUE INDEX jsample_ux1 ON public.jsample USING btree (id, (((jdoc -> 'item'::text) ->> 'id'::text)));
CREATE UNIQUE INDEX jsample_ux2 ON public.jsample USING btree ((((jdoc -> 'item'::text) ->> 'id'::text)));

これで、アップサートは以下のように実行できる。
btree のインデックスでユニークインデックスを作成することがミソ

insert INTO jsample (id, title, jdoc)
VALUES(11, 'test1', jsonb_build_object('A', 11, 'item',jsonb_build_object('id', 'idno_11')) )
ON CONFLICT(id, ((jdoc->'item'::text)->>'id'::text) ) 
DO UPDATE SET title = 'testup123'

または、

insert INTO jsample (id, title, jdoc)
VALUES(11, 'test1', jsonb_build_object('A', 11, 'item',jsonb_build_object('id', 'idno_11')) )
ON CONFLICT(id, ((jdoc->'item'::text)->>'id'::text) ) 
DO UPDATE SET 
title = 'testup123',
jdoc = '{"A": 123,  "item": {"id": "idno_11"}}'::jsonb

DO UPDATE で、JSONB_SET関数で更新を書く時に、ついテーブル名を指定しないと
SQLエラー [42702]: 列参照が曖昧となるので、
DO UPDATE で実行する時は、 JSONB_SET( テーブル名.JSONB型の列名 , path , value , [ boolean ] )
とすること