Postgres JSONB unique constraint

Question

I have a table as following table.

create table person {
  firstname varchar,
  lastname varchar,
  person_info jsonb,
  ..
}

I already have unique constraints on firstname + lastname. I recently identify there is always something different in person_info jsonb. I want to uniquely identify by person_info jsonb.

Should I add person_info as part of unique constraints firstname + lastname + person_info ? Is there any performance impact with such implementation ? I heard JSONB is not good for index when number of data increases.

I am thinking to use store person_info hashvalue in different field and combine this new hashvalue field as part of unique index.

I would appreciate if I get some help from expert on this.

Laurenz Albe · Accepted Answer · 2019-08-08 14:27:12Z

This seems like a wrong idea.

A primary key should be immutable and uniquely identify a table row.

Names are not good for that, because

different people can have the same name
names can change

This is probably why you are tempted to add additional information to truly identify each individual row.

Unless you have some immutable attribute that uniquely identifies each person (such as the social security nubmer), you should generate an artificial primary key for the table:

ALTER TABLE person
   ADD id bigint
      GENERATED ALWAYS AS IDENTITY
      PRIMARY KEY;

Indexing a jsonb is possible, but you will get problems with long values since index entries are limited in size, and you will get an error if you exceed the limit.

I recommend that any attribute that you might want to index is not stored in a jsonb, but as a regular table column.

Andrea Binda · Accepted Answer · 2019-08-08 14:20:58Z

0

JSONB indexing IMHO refers to the ability to index fields inside the binary JSON rather than the whole block. Be aware also that key ordering is not kept! So if you can obtain two different hashes for two json with the exact same data but different ordering. Instead, if you can find which json fields gives you uniqueness, than you can use directly those for indexing.

Try also to look at this page

answered Aug 8, 2019 at 14:20

Andrea Binda

1605 bronze badges

1 Comment

user330315 Over a year ago

For the purpose of creating a unique index, the normalization that JSONB does is actually a good thing. Only then you can use = on two different json values

Collectives™ on Stack Overflow

Postgres JSONB unique constraint

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related