Re: create unique constraint on jsonb->filed during create table

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Wednesday, February 27, 2019, Andy Fan <zhihui.fan1213@xxxxxxxxx> wrote:

The following way works with 2 commands:

zhifan=# create table t1 (a jsonb);

CREATE TABLE

zhifan=# create unique index t1_a_name on t1 ((a->'name'));

CREATE INDEX

but know I want to merge them into 1 command, is it possible?

zhifan=# create table t2 (a jsonb, constraint uk_t2_a_name unique((a->'name')));

ERROR:  syntax error at or near "("

LINE 1: ...table t2 (a jsonb, constraint uk_t2_a_name unique((a->'name'...

Not according to the documentation.  Unique table constraints can only reference columns in the table as a whole.  An _expression_ index must be created separately from the table to which it is attached.

Or add a trigger to the table, populate an actual second column (making it unique), and add a table check constraint that that column and the _expression_ are equal.  I suspect you’ll be happier having the PK as actual column data anyway.

David J.