From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Simple Atomic Relationship Insert |
Date: | 2015-01-13 17:45:22 |
Message-ID: | CAAXGW-ypwn6h56-KeJLuENwgRE9W5wmx_af8LJZ4TTBkCzXatg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)archie(dot)mckown(at)gmail(dot)com>
wrote:
> On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)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 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | John McKown | 2015-01-13 18:05:48 | Re: Simple Atomic Relationship Insert |
Previous Message | John McKown | 2015-01-13 16:50:39 | Re: Simple Atomic Relationship Insert |