Re: create unique constraint on jsonb->filed during create table
Got it, thank you! On Thu, Feb 28, 2019 at 12:48 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, February 27, 2019, Andy Fan > 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. > >
Re: create unique constraint on jsonb->filed during create table
On Wednesday, February 27, 2019, Andy Fan 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.
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'...