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

2019-02-28 Thread Andy Fan
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

2019-02-27 Thread David G. Johnston
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

2019-02-27 Thread Andy Fan
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'...