Postgresql: How to insert or update a record

Question

I need to update the data, and if they are not in the database, then insert and return the key. Unfortunately, I can't use merge, on conflict and procedural blocks. Now I'm using the following query,

WITH _a AS (
  UPDATE mytable1 SET name=expr1, last_name = expr2
  WHERE empId = expr3
  RETURNING empId 
), _b AS (
  INSERT INTO mytable1 (empId, name, last_name)
  SELECT(expr3, expr1, expr2)
  WHERE NOT EXISTS (SELECT * FROM _a)
  RETURNING empId

but unfortunately, if the query inserts data, it returns the key, and if it updates, it doesn't.

Is it possible to somehow rewrite the request so that it always returns the key?

Does this answer your question? [How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?](https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql) — Marc, Mar 23 '23 at 17:51
@Marc As far as I understand, the article is about `INSERT ... ON CONFLICT UPDATE`. But my postgresql version does not allow using this query. I also can't use procedural blocks — Inessa, Mar 23 '23 at 17:57

score 0 · Accepted Answer · answered Mar 23 '23 at 17:53

Is it possible to somehow rewrite the request so that it always returns the key?

You are almost there.

Just use union all to combine results of insert and update statements.

WITH _a AS (
  UPDATE mytable1 SET name = expr1, last_name = expr2
  WHERE empId = expr3
  RETURNING empId 
), _b AS (
  INSERT INTO mytable1 (empId, name, last_name)
  SELECT expr3, expr1, expr2
  WHERE NOT EXISTS (SELECT * FROM _a)
  RETURNING empId 
)
SELECT empId FROM _a
UNION ALL
SELECT empId FROM _b;

Postgresql: How to insert or update a record

1 Answers1