Postgres already has a way to do "upsert" but I don't like sending these long queries, I wonder if it could be implemented as a stored procedure which would accept json and perform

INSERT INTO ... (keys)
  VALUES (values)
  ON CONFLICT DO UPDATE SET (setEverythingFromExcluded)

I know I could somehow use json_object_keys and json_extract_path or maybe directly json_each but my initial attempts lead nowhere so maybe somebody already did this?

  • 5
    Wrapping INSERT in server side procedure? It's doable in any procedural language. But your question is unclear. Do you want SELECT upsert( 'my_table_name', 'my_json_object')? Please do not say "somehow", instead explain what you want (with code samples) and what you did (with code samples) and how it worked. stackoverflow.com/help/how-to-ask – filiprem Apr 26 '18 at 9:01

There is a reason why the UPSERT syntax and functionality was added to Postgres. I wouldn't go into much detail, because it's described exhaustively elsewhere, but most issues with custom upsert implementations stem from concurrency. INSERT ... ON CONFLICT UPDATE ... solves these issues and gives you, in the end, a much more concise syntax than any other solution.

  • 1
    If I understand the OP correctly, he is not trying to avoid INSERT ... ON CONFLICT, but use it for automated processing of JSON input. It's just not clear (yet) how exactly. – Erwin Brandstetter Apr 27 '18 at 3:22
  • @ErwinBrandstetter yeah, exactly - I just dont want to write it (over and over again) – Kamil Tomšík Apr 27 '18 at 11:46
  • 1
    @ErwinBrandstetter feel free to add an answer, I am not in the position to extend this one in the coming days. – dezso Apr 27 '18 at 13:00
  • 1
    Sure, if the OP edits the question to clarify and I find the time before you do. – Erwin Brandstetter Apr 27 '18 at 13:54

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.