From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Simple Atomic Relationship Insert |
Date: | 2015-01-13 16:42:20 |
Message-ID: | CAAXGW-w52WYJuyC0AQg2ut2zVqsW3WX01J+66j6tHJnmSmPt+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 that matches "Portland" or if it doesn't exist I INSERT it
returning the 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 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!
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2015-01-13 16:42:27 | Re: Check that streaming replica received all data after master shutdown |
Previous Message | Tom Lane | 2015-01-13 16:00:56 | Re: Timstamp to Json conversion issue |