Thread: 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