1

Let me use a simple sample to describe my question. Say I have a table t that has only two fields, auto 'id', and a jsonb typed 'info' field:

CREATE TABLE t(
  ID serial NOT NULL PRIMARY KEY,
  info jsonb NOT NULL
);

'info' might have fields 'a', 'b', 'c' as well as some other fields. Field 'c' might be null, and I want to (a, b, c) to be unique, so I created a unique index on them:

CREATE UNIQUE INDEX idx_abc ON t (
  (info ->> 'a'), (info ->> 'b'), (info ->> 'c')
);

But then I realized that, if 'c' is null, this unique constraint doesn't work at all. For example, I can insert multiple rows of:

insert into t(info) values('{"a": "a1", "b": "b1"}')

or

insert into t(info) values('{"a": "a1", "b": "b1", "c": null}')

My current solution is to anyway give "c" a value, if "c" is null, then I set "null":

insert into t(info) values('{"a": "a1", "b": "b1", "c": "null"}')

this way will trigger the unique constraint if I run the insert twice. But I feel this is just a workaround, I wonder if there is a better to solution to allow the unique constraint to work even if field "c" is null.

TieDad
  • 9,143
  • 5
  • 32
  • 58

1 Answers1

3

If it's just the one key that might have a NULL value, partial indexes would be an efficient solution, like outlined here:

A more general (less efficient) solution is to use COALESCE:

CREATE UNIQUE INDEX idx_abc ON t (
   COALESCE(info ->> 'a', 'NULL')
 , COALESCE(info ->> 'b', 'NULL')
 , COALESCE(info ->> 'c', 'NULL')
);

This way you fold actual NULL values and the string 'NULL' (arbitrary example) to the same value. Much like you already do, but you don't need to mess with original column values.

I would also consider extracting involved keys from the jsonb document as regular Postgres columns (redundantly or not) to have the full arsenal of relational tools at your disposal: (NOT NULL) constraints, default values, etc. Faster, too.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This did not work for me: `COALESCE(info ->> 'a', 'NULL')` i had to change it to `COALESCE(info ->> 'a', NULL)` then it worked. Thanks for the example nonetheless, it was very helpful. – Javier Buzzi Jun 01 '19 at 12:18
  • 1
    @JavierBuzzi: This is a misunderstanding. The `COALESCE` wrapper in `COALESCE(info ->> 'a', NULL)` is a no-op (does nothing). The whole point here is to replace `NULL` values with a non-null value, so the `UNIQUE` constraint kicks in. Follow the link above for more explanation. – Erwin Brandstetter Jun 01 '19 at 12:27
  • Maybe for OP's example, for mine, i want to ignore it if it is not there. – Javier Buzzi Jun 01 '19 at 12:48
  • Not sure about your objective, but `COALESCE(info ->> 'a', NULL)` can *always* be replaced with just `info ->> 'a'`. – Erwin Brandstetter Jun 01 '19 at 13:24