Обсуждение: Unique constraint on field inside composite type.

On Wed, Aug 17, 2016 at 23:02:53 -0700,
  Silk Parrot <silkparrot@gmail.com> wrote:
>Hi,
>
>    I am trying to model a social login application. The application can support multiple login providers. I am
thinkingof creating a custom type for each provider. e.g. 
>
>CREATE TABLE user (
>    uuid UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),
>    google_user system.google_user,
>    facebook_user system.facebook_user,
>    UNIQUE (google_user.email)
>);

Wouldn't it more sense to have a table you join to your user table that
is more flexible and allows for multiple entries per person. You would
need user, domain, foreign_user, auth_method. This would make it a lot
easier to add other systems later or let users pick their own systems
that you don't need to know about in advance.

On 08/17/2016 11:02 PM, Silk Parrot wrote:
> Hi,
>
>     I am trying to model a social login application. The application can
> support multiple login providers. I am thinking of creating a custom
> type for each provider. e.g.
>
> CREATE TYPE system.google_user AS (
>    email TEXT
> );
>
> CREATE TYPE system.facebook_user AS (
>    id TEXT
> );
>
>    And having user table like:
>
> CREATE TABLE user (
>     uuid UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),
>     google_user system.google_user,
>     facebook_user system.facebook_user,
>     UNIQUE (google_user.email)
> );
>
> However, the above create table query reports syntax error:
>
> ERROR:  syntax error at or near "."
> LINE 10:     UNIQUE (google_user.email)
>
> Is there a way to create unique constraint on a field inside composite type?

I tried David's suggestion:

(google_user).email

and that did not work, but it got me to thinking, so:

CREATE OR REPLACE FUNCTION public.comp_type_idx(google_user)
  RETURNS character varying
  LANGUAGE sql
AS $function$ SELECT $1.email $function$

CREATE TABLE test_user (
     google_user google_user,
     facebook_user facebook_user
);

create unique index g_u on  test_user (comp_type_idx(google_user));

test=# insert into test_user values (ROW('email'), ROW(1));
INSERT 0 1

test=# insert into test_user values (ROW('email'), ROW(1));
ERROR:  duplicate key value violates unique constraint "g_u"
DETAIL:  Key (comp_type_idx(google_user))=(email) already exists.

test=# insert into test_user values (ROW('email2'), ROW(1));
INSERT 0 1

test=# select * from test_user ;
  google_user | facebook_user
-------------+---------------
  (email)     | (1)
  (email2)    | (1)
(2 rows)





>
> --
> Regards
> Ryan


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 08/17/2016 11:02 PM, Silk Parrot wrote:
>> CREATE TABLE user (
>> uuid UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),
>> google_user system.google_user,
>> facebook_user system.facebook_user,
>> UNIQUE (google_user.email)
>> );
>> ERROR:  syntax error at or near "."
>> LINE 10:     UNIQUE (google_user.email)
>>
>> Is there a way to create unique constraint on a field inside composite type?

> I tried David's suggestion:
> (google_user).email
> and that did not work, but it got me to thinking, so:

You'd need additional parens around the whole thing, like

create unique index on "user"(((google_user).email));

The UNIQUE-constraint syntax will never work, because per SQL standard
such constraints can only name simple columns.  But you can make
a unique index separately.

            regards, tom lane

On 08/22/2016 06:23 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 08/17/2016 11:02 PM, Silk Parrot wrote:
>>> CREATE TABLE user (
>>> uuid UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),
>>> google_user system.google_user,
>>> facebook_user system.facebook_user,
>>> UNIQUE (google_user.email)
>>> );
>>> ERROR:  syntax error at or near "."
>>> LINE 10:     UNIQUE (google_user.email)
>>>
>>> Is there a way to create unique constraint on a field inside composite type?
>
>> I tried David's suggestion:
>> (google_user).email
>> and that did not work, but it got me to thinking, so:
>
> You'd need additional parens around the whole thing, like
>
> create unique index on "user"(((google_user).email));

Aah, I did not bury it deep enough, my attempt:

create unique index g_u on  test_user ((google_user).email));

>
> The UNIQUE-constraint syntax will never work, because per SQL standard
> such constraints can only name simple columns.  But you can make
> a unique index separately.
>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com