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);
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
