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 по дате отправления: