[HACKERS] Fetch JSONB Value for UNIQUE Constraint

[David E. Wheeler]

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
Description: S/MIME cryptographic signature