From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Fetch JSONB Value for UNIQUE Constraint |
Date: | 2017-03-24 23:57:38 |
Message-ID: | 47E3DF5A-EF5E-4FA9-9477-08CCCC37023C@justatheory.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Dear Hackers,
Should this work?
CREATE TABLE things (
user_id INTEGER NOT NULL,
document JSONB NOT NULL,
UNIQUE (user_id, document->>'name')
);
ERROR: syntax error at or near "->>"
LINE 4: UNIQUE (user_id, document->>’name')
I tried adding parens, but that didn’t work, either:
CREATE TABLE things (
user_id INTEGER NOT NULL,
document JSONB NOT NULL,
UNIQUE (user_id, (document->>'name'))
);
ERROR: syntax error at or near "("
LINE 4: UNIQUE (user_id, (document->>'name'))
It works fine to create a unique index, though:
CREATE TABLE things (
user_id INTEGER NOT NULL,
document JSONB NOT NULL
);
CREATE UNIQUE INDEX ON things(user_id, (document->>'name'));
So it’s a fine workaround, but maybe there’s something missing from the parsing of the CREATE TABLE statement? This is on 9.6.1.
Best,
David
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2017-03-25 00:00:05 | Re: Fetch JSONB Value for UNIQUE Constraint |
Previous Message | Andrew Gierth | 2017-03-24 23:39:30 | Re: Hash support for grouping sets |