Search Postgresql Archives

Re: Simple Atomic Relationship Insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



John McKown wrote:
On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco
<robert.difalco@xxxxxxxxx <mailto:robert.difalco@xxxxxxxxx>>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



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux