Primary key within JSON data in Postgresql
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.