Обсуждение: Fetch JSONB Value for UNIQUE Constraint
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 on9.6.1. Best, David
On Fri, Mar 24, 2017 at 4:57 PM, David E. Wheeler <david@justatheory.com> wrote: > So it’s a fine workaround, but maybe there’s something missing from the parsing of the CREATE TABLE statement? This ison 9.6.1. Unique constraints don't support expressions, or a predicate (partial-ness). -- Peter Geoghegan
On Mar 24, 2017, at 5:00 PM, Peter Geoghegan <pg@bowt.ie> wrote: >> So it’s a fine workaround, but maybe there’s something missing from the parsing of the CREATE TABLE statement? This ison 9.6.1. > > Unique constraints don't support expressions, or a predicate (partial-ness). Oh. Okay. I assumed the syntax would be identical to a unique index, since that’s ultimately what a unique constraint is,IIUC. My mistake. Thanks Peter! Best, David