Обсуждение: create unique constraint on jsonb->filed during create table
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'...
On Wednesday, February 27, 2019, Andy Fan <zhihui.fan1213@gmail.com> wrote:
The following way works with 2 commands:zhifan=# create table t1 (a jsonb);CREATE TABLEzhifan=# create unique index t1_a_name on t1 ((a->'name'));CREATE INDEXbut 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.
Got it, thank you!
On Thu, Feb 28, 2019 at 12:48 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, February 27, 2019, Andy Fan <zhihui.fan1213@gmail.com> wrote:
The following way works with 2 commands:zhifan=# create table t1 (a jsonb);CREATE TABLEzhifan=# create unique index t1_a_name on t1 ((a->'name'));CREATE INDEXbut 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.