Re: Simple Atomic Relationship Insert

[Robert DiFalco <robert.difalco@xxxxxxxxx>]

Thanks John. I've been seeing a lot of examples like this lately. Does the following approach have any advantages over traditional approaches?

WITH sel AS (
    SELECT id FROM hometowns WHERE name = 'Portland'
), ins AS (
  INSERT INTO hometowns(name)
    SELECT 'Portland'
    WHERE NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
)
INSERT INTO users(name, hometown_id)

    VALUES ('Robert', SELECT id FROM ins UNION ALL SELECT id FROM sel);

On Tue, Jan 13, 2015 at 8:50 AM, John McKown <john.archie.mckown@xxxxxxxxx> wrote:

On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco <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 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!

​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.

--

​

While a transcendent vocabulary is laudable, one must be eternally careful so that the calculated objective of communication does not become ensconced in obscurity.  In other words, eschew obfuscation.

111,111,111 x 111,111,111 = 12,345,678,987,654,321

Maranatha! <><
John McKown