Thread: Unique constraint on field inside composite type.
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
Regards
Ryan
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.
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