Обсуждение: jsonb unique constraints
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)
);
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 ->>'.
On Tue, May 5, 2020 at 8:33 AM Ted Toth <txtoth@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.
I was able to create a unique index, thanks.
On Tue, May 5, 2020 at 10:38 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, May 5, 2020 at 8:33 AM Ted Toth <txtoth@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.