Re: [GENERAL] Unique constraint on field inside composite type.

[Adrian Klaver]

On 08/22/2016 06:23 PM, Tom Lane wrote:

Adrian Klaver  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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique constraint on field inside composite type.

[Tom Lane]

Adrian Klaver  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique constraint on field inside composite type.

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique constraint on field inside composite type.

[Bruno Wolff III]

On Wed, Aug 17, 2016 at 23:02:53 -0700,
 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 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.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique constraint on field inside composite type.

[David G. Johnston]

On Thu, Aug 18, 2016 at 2:02 AM, Silk Parrot  wrote:

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

​Not tested here but in most (all?) cases when attempting to de-reference a
component of a composite typed column you must place the column name within
parentheses.

(google_user).email

Otherwise the system is thinking that "google_user" is a schema and email
is a column.

David J.​

[GENERAL] Unique constraint on field inside composite type.

[Silk Parrot]

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?

-- 
Regards
Ryan