So to simplify, what I understand is
1. INSERT .. ON CONFLICT is an implementation UPSERT technique
2. MERGE is something meant to be different from UPSERT but has UPSERT feature?
Postgres gets support for upsert
81–90 of 114 posts
Re: Postgres gets support for upsert
#82As someone that's been using PostgreSQL way before it was cool (since 2005), I'm super happy to see this. Previously I had to do EXISTS, IF FOUND UPDATE ELSE INSERT. Now I can just do upsert, so nice.
Very thankful to all the hard working Postgres devs. PostgreSQL was really good when I first started using it, and just keeps getting better. Glad to see the general community finally getting on the PostgreSQL bandwagon.
Now let's see if I can get more people to use internal PostgreSQL functions instead of constantly writing statements directly in their code.
Re: Postgres gets support for upsert
#83I know it doesn't matter to some developers out there, but for me, the only thing left is the ability to add a column after another one. Even if it's only logical order, it's easier for me to read a table structure when fields are ordered nicely.
I'd love this too. I wanted materialised views and upsert, so here's hoping :)
Re: Postgres gets support for upsert
#84Slightly OT, why do we see these errors with Oracle's MERGE statements?
ORA-01779: cannot modify a column which maps to a non key-preserved table
ORA-30926: unable to get a stable set of rows in the source tables
Re: Postgres gets support for upsert
#85Slightly OT, why do we see these errors with Oracle's MERGE statements? ORA-01779: cannot modify a column which maps to a non key-preserved table ORA-30926: unable to get a stable set of rows in the source tables
Asking a support question on a feature announcement post for a completely unrelated product is EXTREMELY off topic.
Re: Postgres gets support for upsert
#86I've been looking forward to this for ages! The solution we use at the moment for upsert is a create or replace rule on the table but it isn't great as you need to remember to update the rule whenever you change the table schema...
CREATE OR REPLACE RULE "replace_row" AS
ON INSERT TO my_table
WHERE EXISTS(SELECT 1 FROM my_table WHERE id=NEW.id)
DO INSTEAD
(UPDATE my_table SET
col1=NEW.col1,
col2=NEW.col2,
col3=NEW.col3,
WHERE id=NEW.id);Re: Postgres gets support for upsert
#87Slightly OT, why do we see these errors with Oracle's MERGE statements? ORA-01779: cannot modify a column which maps to a non key-preserved table ORA-30926: unable to get a stable set of rows in the source tables
Asking a support question on a feature announcement post for a completely unrelated product is EXTREMELY off topic.
It was not meant as a support question. It was in the context of MERGE/UPSERT was I asking. I wanted to know if similar thing happens in PG.
Re: Postgres gets support for upsert
#88Does anyone know when this is expected to be released?
New versions of Postgres usually ship in September. Except 9.4, which slipped to December.
Re: Postgres gets support for upsert
#89Earlier quoted context omitted.
It's fairly trivial to do with a savepoint, but it's definitely not performant, and requires some pretty stupid client-side error-handling. It will also cause your Postgres logs to be littered with benign but misleading error messages (possibly these could be silenced, I never bothered to find out). So, yes, this is great news.
Minor nit: it's performant and doesn't pollute logs to implement this on the server side. I've written macros to make gen'ing upsert functions easy, given a certain table pattern. But this definitely cleans that situation up as well as makes it accessible to more casual developers.
Yes, sorry, I was referring to doing it client-side.
Re: Postgres gets support for upsert
#90I've been looking forward to this for ages! The solution we use at the moment for upsert is a create or replace rule on the table but it isn't great as you need to remember to update the rule whenever you change the table schema... CREATE OR REPLACE RULE "replace_row" AS ON INSERT TO my_table WHERE EXISTS(SELECT 1 FROM my_table WHERE id=NEW.id) DO INSTEAD (UPDATE my_table SET col1=NEW.col1, col2=NEW.col2, col3=NEW.co…
That unfortunately is not safe on several fronts.
For one, the WHERE EXISTS() will not see concurrent insertions by transactions that are still in progress, which means you'll still get constraint violation errors.
For another, rules generally have very surprising behaviour. E.g. with this RULE you'll get into trouble if "id" isn't passed in as a explicit value, but uses a non-deterministic DEFAULT or directly passed in expression. Every reference to NEW.id will not be replaced by the result of that expression, but rather with the expression itself. For the common serial id column (aka autoincrement) you'll have a separate nextval() call in each reference. Which can make this explode in pretty damn confusing ways.