Unique index on hash of jsonb value - correct solution?
От | Albrecht Dreß |
---|---|
Тема | Unique index on hash of jsonb value - correct solution? |
Дата | |
Msg-id | RHKB2EIQ.B7PUNVXM.MTKKMOHG@B6UPJ7T6.RKJUGJDU.XX7XFT3J обсуждение исходный текст |
Ответы |
Re: Unique index on hash of jsonb value - correct solution?
|
Список | pgsql-general |
Hi all, in a database I have a table with a text, a jsonb and a bytea column, which together shall be unique, like: <snip> Column | Type | Collation | Nullable | Default ---------+--------+-----------+----------+------------------------------------- id | bigint | | not null | nextval('demotab_id_seq'::regclass) textval | text | | not null | jsonval | jsonb | | not null | blobval | bytea | | not null | Indexes: "demo_idx" UNIQUE, btree (textval, jsonval, blobval) </snip> This seems to work just fine in most cases, but adding a bigger json value (the text and the bytea columns are always short)results in a “index row size 2840 exceeds maximum 2712 for index "…"” error. Following the hint in the error message,I replaced the index by <snip> Indexes: "demo_idx" UNIQUE, btree (textval, md5(jsonval::text), blobval) </snip> which seems to solve the issue. My question: is this approach (a) correct and (b) still safe if the items in the jsonb (always a dict in my case) are re-ordered? I tested a few cases, and trying to insert something like e.g. '{"a":1,"b":2}' and '{"b":2,"a":1}' actually doesproduce the same hash (i.e. the 2nd insert attempt is rejected due to the index), but is this guaranteed by design forevery case? Or is there a better solution for this use case? Thanks in advance, Albrecht.
Вложения
В списке pgsql-general по дате отправления: