UPSERT means INSERT and\or UPDATE, is there a standard name for a stored procedure that will INSERT if necessary and then SELECT?

e.g. I want to get the IS of a row that matches columns and give me the ID, if nothing matched then I want it to INSERT a new row and return me the ID of that row.

  • Similar question has been answered here. stackoverflow.com/questions/3593870/… – user353gre3 Apr 3 '14 at 14:35
  • @BinayaRegmi, that question seems to be about implementation (which is useful as well), I just wondered if thare was a naming convention for it... Merge maybe? – BanksySan Apr 3 '14 at 14:49
  • 2
    MERGE is the actual SQL statement that performs the "upsert" function. I don't think there is a name for what you are describing. May be call it "inselect"? – mustaccio Apr 3 '14 at 15:09
  • 2
    In other contexts, that would look like a get or create pattern. The naming, obviously, is not very much SQLish. – joanolo Feb 2 '17 at 20:42

In PostgreSQL we have a name for it INSERT ... RETURNING.

You can use this form in a CTE to obtain the effect you want..

CREATE TABLE foo ( foo serial, bar int );

WITH t1 AS (
  FROM generate_series(1,10) AS t(x)
t2 AS (
  INSERT INTO foo (bar)
  FROM t1
    SELECT *
    FROM foo
    WHERE foo.bar = x
  RETURNING foo, bar
SELECT foo, bar
FROM foo
INNER JOIN t1 ON (foo.bar = t1.x)
  FROM t2

Here we

  1. Generate test data in a cte t1 which we plan to INSERT or SELECT on.
  2. With t2 we INSERT the data we don't already have into our desired table... foo, and return the results (that which was inserted) as t2
  3. We run a SELECT to get all of the elements in our test data (t1) that already exist.
  4. INSERT all of the others.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.