Re: Simple Atomic Relationship Insert - Mailing list pgsql-general

John McKown wrote:
> On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco
> <robert.difalco@gmail.com <mailto:robert.difalco@gmail.com>>wrote:
>
>     Let's say I have two tables like this (I'm leaving stuff out for
>     simplicity):
>
>     CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
>     CREATE TABLE hometowns (
>        id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
>        name VARCHAR,
>        PRIMARY KEY (id),
>        UNIQUE(name)
>     );
>
>     CREATE SEQUENCE USER_SEQ_GEN START 1 INCREMENT 1;
>     CREATE TABLE users (
>        id              BIGINT DEFAULT nextval('USER_SEQ_GEN'),
>        hometown_id     INTEGER,
>        name            VARCHAR NOT NULL,
>        PRIMARY KEY (id),
>        FOREIGN KEY (hometown_id) REFERENCES hometowns(id)
>     );
>
>     The hometowns table is populate as users are created.  For example,
>     a client may submit {"name":"Robert", "hometown":"Portland"}.
>
>     The hometowns table will never be updated, only either queries or
>     inserted.
>
>     So given this I need to INSERT a row into "users" and either SELECT
>     the hometowns.id <http://hometowns.id> that matches "Portland" or if
>     it doesn't exist I INSERT it returning the hometowns.id
>     <http://hometowns.id>".
>
>     Normally I would do by first doing a SELECT on hometown. If I don't
>     get anything I do an INSERT into hometown RETURNING the id. If THAT
>     throws an error then I do the SELECT again. Now I'm finally ready to
>     INSERT into users using the hometowns.id <http://hometowns.id> from
>     the above steps.
>
>     But wow, that seems like a lot of code for a simple "Add if doesn't
>     exist" foreign key relationship -- but this is how I've always done.
>
>     So my question. Is there a simpler, more performant, or thread-safe
>     way to do this?
>
>     Thanks!
>
>
> ​What occurs to me is to simply do an INSERT into the "hometowns" table
> and just ignore the "already exists" return indication. Then do a SELECT
> to get the hometowns​ id which now exists, then INSERT the users. but I
> could easily be overlooking some reason why this wouldn't work properly.

And one more approach:

CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
CREATE TABLE hometowns (
   id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
   name VARCHAR,
   PRIMARY KEY (id),
   UNIQUE(name)
);

CREATE SEQUENCE USER_SEQ_GEN START 1 INCREMENT 1;
CREATE TABLE users (
   id              BIGINT DEFAULT nextval('USER_SEQ_GEN'),
   hometown_id     INTEGER,
   name            VARCHAR NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (hometown_id) REFERENCES hometowns(id)
);

create or replace view user_town as
   select
    users.name     as username,
    hometowns.name as hometownname
   from users
   join hometowns
    on hometowns.id = users.hometown_id;

create rule user_town_exists as on insert to user_town
   where exists(select id from hometowns where (hometowns.name =
new.hometownname))
   do
     insert into users (name, hometown_id)
       values (new.username, (select id from hometowns where
(hometowns.name = new.hometownname)));

create rule user_town_not_exists as on insert to user_town
   where not exists(select id from hometowns where (hometowns.name =
new.hometownname))
   do (
     insert into hometowns (name) values (new.hometownname);
     insert into users (name, hometown_id)
       values (new.username, (select id from hometowns where
(hometowns.name = new.hometownname)));
   );

create rule user_town_nothing as on insert to user_town
   do instead nothing;


---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com