Fetch JSONB Value for UNIQUE Constraint

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Fetch JSONB Value for UNIQUE Constraint

David E. Wheeler-3
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


smime.p7s (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fetch JSONB Value for UNIQUE Constraint

Peter Geoghegan-4
On Fri, Mar 24, 2017 at 4:57 PM, David E. Wheeler <[hidden email]> wrote:
> 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.

Unique constraints don't support expressions, or a predicate (partial-ness).


--
Peter Geoghegan


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Fetch JSONB Value for UNIQUE Constraint

David E. Wheeler-3
On Mar 24, 2017, at 5:00 PM, Peter Geoghegan <[hidden email]> wrote:

>> 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.
>
> 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


smime.p7s (5K) Download Attachment