CODEWITHSUNDEEP
sqlpostgresqlunique-constraint
writofmandamus
writofmandamus

Reputation: 1231

How to set unique constraint on nested JSON with multiple columns

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?

Upvotes: 2

Views: 2588

Answers (1)

cske
cske

Reputation: 2243

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 ) }

Upvotes: 5

Related Questions