Primary key within JSON data in Postgresql

Question

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

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

I can create a UNIQUE INDEX on id, thus:

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

---

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

> 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:

> 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?

Answer 1

No, you should not be able to add such a constraint.

The PRIMARY KEY must be a simple or composite index. It may not be a partial or expression index.

The index acts as a constraint, and functionally they're much the same, but it can't appear as the PRIMARY KEY in the table metadata and cannot be used as the target of a foreign key constraint. The same is true for UNIQUE constraints.

The issue here is that the SQL-standard definitions of PRIMARY KEY and UNIQUE constraints do not allow for expressions or for row matching predicates. So if PostgreSQL lists an expression index or partial index as a constraint, it's breaking the standard and lying to applications about what it's doing. Apps that understand PostgreSQL's features can look the index up from Pg's own catalogs, and there's also info in information_schema, but it can't go in as a listed constraint.

• davetapley: Can you cite any docs for this? I couldn't find any. Also, do you think I should be able to ADD CONSTRAINT t_unique_id UNIQUE as shown in my second example?
• Craig Ringer: No, it shouldn't because a UNIQUE constraint cannot be partial or contain expressions. The issue is that SQL specifies what a PRIMARY KEY or UNIQUE constraint is - PostgreSQL can't just redefine what they mean because they're part of the standard. It can and does provide extensions, but if it redefined the standard constraint types then apps that depended on their meanings might break. As for docs: No specific reference; Pg only recently added the ability to create a constraint using an existing index in the first place. Should add something to the docs...
• davetapley: Thanks. In your answer you say, "You can have a UNIQUE constraint", do you just mean 'in general', then? I got excited reading that because I thought you meant "You can have a UNIQUE constraint"...on an expression index.
• Craig Ringer: Apologies, poor wording on my part. Out of interest, why do you want to do this? What purpose does defining a constraint serve? The index already constrains the values, it just isn't listed in the catalogs as a formal constraint.
• davetapley: Thanks for the edit, I just wanted to be sure I was understanding! R.e. why: Up until a few hours ago I'd always assumed that a specifying unique on an index was just a hint to the DB that the values were unique, but if you wanted uniqueness enforced you needed an explicit constraint. As I now understand it, they're just two sides of the same coin, per this question :)

Answer 2

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->>'id') IS NOT NULL); 
  

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

  CREATE UNIQUE INDEX t_id ON t ((json->>'id'))
  

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

Indexes:
 "t_id" UNIQUE, btree ((json ->> 'id'::text))
Check constraints:
 "id_not_null" CHECK ((json ->> '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 ->> '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.