JSONB の primary key を作る ( by PosetgreSQL ) - Oboe吹きプログラマの黙示録

単純に CREATE TABLE の PRIMARY KEY で指定してもエラーになる。

CREATE TABLE public.jsample (
      id int4 NOT NULL,
      title varchar(60) NOT NULL,
      jdoc jsonb not NULL,
      PRIMARY KEY (id, jdoc->'item'->>'id')
);

SQLエラー [42601]: ERROR: "->"またはその近辺で構文エラー
PRIMARY KEY 複合の場合、一旦、JSONB型の列を指定しないで TABLE CREATE する。

CREATE TABLE public.jsample (
	id int4 NOT NULL,
	title varchar(60) NOT NULL,
	jdoc jsonb not NULL,
	PRIMARY KEY (id)
);

UNIQUE インデックスと、NOT NULL制約を JSONパスに対して指定する。

DROP INDEX IF EXISTS jsample_ix1;
CREATE UNIQUE INDEX jsample_ix1 ON jsample ((jdoc->'item'->>'id'));
ALTER TABLE jsample ADD CONSTRAINT jsample_jdockey_not_null CHECK ((jdoc->'item'->>'id') IS NOT NULL);

”item" の下、"id" のテキストが、PrimaryKey 指定の id と一緒に、複合の
Primary Key になる。

でも、このような複合による制約の場合、UPSERT を書いた時に、
ON CONFLICT だけで対象インデックスを省略すれば、存在する全ての制約を自動でチェックして　
DO NOTHING ならうまくいくが、DO UPDATE では、
　 SQLエラー [42601]: ERROR: ON CONFLICT DO UPDATE は推定指定または制約名を必要
になる。

ON CONFLICT(id, ((jdoc->'item'::text)->>'id'::text) )

では、SQLエラー [42P10]: ERROR: ON CONFLICT 指定に合致するユニーク制約または排除制約がありません
になってしまう。
ON CONFLICT ON CONSTRAINT ｛制約名｝
制約名を２つ、カンマで区切って指定なんてできないので、
id と JSONB 列内のキーの２個の制約を作れれば、do update も書けるようになる。

上のテーブルで、id が PrimaryKey でなければ、

ON CONFLICT(((jdoc->'item'::text)->>'id'::text) )
DO UPDATE SET

は、成立する。

続き、、、
https://oboe2uran.hatenablog.com/entry/2024/01/27/222041