How to set unique constraint on nested JSON with multiple columns

Question

I'm trying to set a unique constraint between two columns (name and a), one of type text, and one of type json. How do I structure this in Postgres 9.6.3?

An example row looks like:

{ name: 'someName',
  a: {
    b: {
      c: 'some text'
    }
  }
}

What is the postgres command to create a table for this? Currently I have this, which gives a syntax error near (.

CREATE TABLE action ( id bigint NOT NULL, name text, a json, unique (name, payload::json#>>'{message, payload, content}') );

I've also tried

CREATE TABLE action (
    id bigint NOT NULL,
    name text,
    a json
);
ALTER TABLE actions ADD CONSTRAINT actions_constraint UNIQUE (payload::json#>>'{b, c, d}', name);

which keeps giving me this error:

ERROR:  syntax error at or near "::"

How can I create this constraint?

cske · Accepted Answer · 2017-08-09 17:50:31Z

5

If you check documentation create table

and table_constraint is:

[CONSTRAINT constraint_name ]

UNIQUE ( column_name [, ... ] ) index_parameters |

UNIQUE in CREATE TABLE only accepts column names

So if you want index on an expression, you must add it after table created, command for that is CREATE INDEX (not accomplishable by alter table)

create unique INDEX actions_constraint on action (((a#>>'{b, c}')::text), name);

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ] ( { column_name | ( expression ) }

edited Aug 9, 2017 at 17:50

answered Aug 9, 2017 at 4:04

cske

2,2334 gold badges27 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

writofmandamus Over a year ago

Thanks, what actually worked for me is to remove the a from {a, b, c}. So the answer is create unique INDEX actions_constraint on action (((a#>>'{b, c}')::text), name); . Can you confirm this and change it so I can upvote your answer?

Collectives™ on Stack Overflow

How to set unique constraint on nested JSON with multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related