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
