Re: jsonb unique constraints

I was able to create a unique index, thanks.

On Tue, May 5, 2020 at 10:38 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, May 5, 2020 at 8:33 AM Ted Toth <txtoth(at)gmail(dot)com> wrote:
>
>> Can you have unique constraints on jsonb columns keys? I've looked for
>> examples but haven't found any what is the proper syntax? Here's what I
>> tried:
>>
>> CREATE TABLE report_json (
>> recnum int,
>> id integer,
>> report jsonb,
>> PRIMARY KEY (recnum),
>> CONSTRAINT report_json_unique_constraint UNIQUE (id,((report ->>
>> 'data')::int),((report ->> 'ctc')::int),((report ->> 'dtg')::int)
>> );
>>
>> which causes a syntax error at the first '(' around 'report ->>'.
>>
>
> Documentation says constraints must reference column names - so no. You
> can probably get what you are after by directly creating a unique index
> though - those allow expressions.
>
> David J.
>