From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Silk Parrot <silkparrot(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Unique constraint on field inside composite type. |
Date: | 2016-08-23 00:42:56 |
Message-ID: | 5140c3f0-41f7-5bd3-8fe1-edb6e4d89428@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-08-23 01:02:57 | Re: Why insertion throughput can be reduced with an increase of batch size? |
Previous Message | Bruno Wolff III | 2016-08-22 20:44:54 | Re: Unique constraint on field inside composite type. |