Primary key within JSON data in Postgresql I have a table t, with a column called json, of type JSON. Within the JSON there is a natural key:

&gt; SELECT json-&gt;'id' AS id FROM t LIMIT 1;
             id              
-----------------------------
 " 63631ff3809de7a17398602f"


I can create a UNIQUE INDEX on id, thus:

&gt; CREATE UNIQUE INDEX t_id ON t((json-&gt;&gt;'id'));                                                                                                                       

CREATE INDEX




I'd like to add this as a table_constraint_using_index, but it fails for both PRIMARY KEY:

&gt; ALTER TABLE t ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX t_id;                                                                                   
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_pkey
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.


and UNIQUE:

&gt; ALTER TABLE t ADD CONSTRAINT t_unique_id UNIQUE USING INDEX t_id;
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_unique_id...
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.




Should I be able to add such a constraint?
As explained in Craig's answer neither PRIMARY KEY nor UNIQUE can be used.

However,here is a two part solution to work around this:


To enforce not null, use a check constraint, thus:

ALTER TABLE t ADD CONSTRAINT id_not_null CHECK ((json-&gt;&gt;'id') IS NOT NULL); 

To enforce uniqueness, create a unique index*, thus:

CREATE UNIQUE INDEX t_id ON t ((json-&gt;&gt;'id'))



After this, a \d t on in psql will give you:

Indexes:
    "t_id" UNIQUE, btree ((json -&gt;&gt; 'id'::text))
Check constraints:
    "id_not_null" CHECK ((json -&gt;&gt; 'id'::text) IS NOT NULL)


And you can see both not null and uniqueness are enforced:

test=# INSERT INTO t VALUES ('{"no id" : "field here"}');
ERROR:  new row for relation "t" violates check constraint "id_not_null"
DETAIL:  Failing row contains ({"no id" : "field here"}).

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
INSERT 0 1

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
ERROR:  duplicate key value violates unique constraint "t_id"
DETAIL:  Key ((json -&gt;&gt; 'id'::text))=(abc) already exists




* note that the mere existence of the t_id index is sufficient to enforce uniqueness, even though it isn't explicitly given as a constraint.