[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: create unique constraint on jsonb->filed during create table
From:       Andy Fan <zhihui.fan1213 () gmail ! com>
Date:       2019-02-28 14:55:54
Message-ID: CAKU4AWoxGHK+Ntu3BiyPQ4FDPPPvC7Dqy+Zd2Ssj=V_6kKyS7A () mail ! gmail ! com
[Download RAW message or body]

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 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.
>
>

[Attachment #3 (text/html)]

<div dir="ltr">Got it, thank you!<br></div><br><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">On Thu, Feb 28, 2019 at 12:48 PM David G. Johnston \
&lt;<a href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Wednesday, February \
27, 2019, Andy Fan &lt;<a href="mailto:zhihui.fan1213@gmail.com" \
target="_blank">zhihui.fan1213@gmail.com</a>&gt; wrote:<br><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><br>The following way works with 2 \
commands:<div><br></div><div><div>zhifan=# create table t1 (a \
jsonb);</div><div>CREATE TABLE</div><div>zhifan=# create unique index t1_a_name on t1 \
((a-&gt;&#39;name&#39;));</div><div>CREATE INDEX</div></div><div><br></div><div>but \
know I want to merge them into 1 command, is it \
possible?</div><div><br></div><div><div>zhifan=# create table t2 (a jsonb, constraint \
uk_t2_a_name unique((a-&gt;&#39;name&#39;)));</div><div>ERROR:   syntax error at or \
near &quot;(&quot;</div><div>LINE 1: ...table t2 (a jsonb, constraint uk_t2_a_name \
unique((a-&gt;&#39;name&#39;...</div></div></div></div></div></blockquote><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>David \
J.</div><div><br></div> </blockquote></div>



[prev in list] [next in list] [prev in thread] [next in thread]