On Tue, May 5, 2020 at 8:33 AM Ted Toth <txt...@gmail.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.
