From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: create unique constraint on jsonb->filed during create table |
Date: | 2019-02-28 04:47:58 |
Message-ID: | CAKFQuwbGEE-vVnFvtY-xQ7pHkQ=PAP9W-z=1TKe_VFL0yKgqKg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wednesday, February 27, 2019, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2019-02-28 07:08:42 | Re: Barman disaster recovery solution |
Previous Message | Andy Fan | 2019-02-28 04:32:34 | create unique constraint on jsonb->filed during create table |