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:

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

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.

StackExchange Database Administrators Q#64270, answer score: 6