Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux