Say oops, UPSERT your head: PostgreSQL version 9.5 has landed PostgreSQL has pitched its latest release with a cheeky dig at MySQL as a "legacy" database. Version 9.5 of PostgreSQL adds a feature called UPSERT – INSERT, ON CONFLICT UPDATE – which it says removes the last barrier for MySQL users to migrate. The feature targets web and mobile environments, by handling conflicts between …
It looks from a quick scan of the proposed PG 9.5 manual changes that this is a reasonable approach given that the ETL crowd may find ON CONFLICT IGNORE clauses useful.
Using the proposed ON CONFLICT clause is, on the face of it, a good way to go because it looks as though not using the clause retains the current behavior, i.e. adding this SQL extension won't harm existing code and you don't have to use the ON CONFLICT clause if you don't want to.
Not using it suits my style because I tend to check whether a row with a unique key exists before attempting an insert rather than trying the insert anyway and handling the exception if the key already exists. This is probably due to over-long exposure to using 4GLs and COBOL to update ISAM files.
Not quite sure what you are driving at - the "UPSERT" functionality basically requires a unique constraint to function, I don't think it's leading down the path of MyISAM-style poor design. It's not a direct equivalent to MySQL's MERGE facility, and anyone wanting to port sloppy practices as-is won't have much fun with Postgres anyway.
I don't think that's relevant. UPSERT is great because it allows you to replace possibly multiple queries (insert of new values, update of existing ones) with corresponding correlated subqueries with a declarative one: INSERT ON CONFLICT IGNORE; or, INSERT ON CONFLICT UPDATE which will respect existing constraints. UPSERT, by definition, is irrelevant for tables without unique constraints such as primary keys.
This is much, much easier for both man and machine to understand and has added the advantage of putting giving responsibility for optimising the correlated subqueries to the query optimiser.
> "I am a postgres fan, but when I make a table without a database driven primary key which kind of requires an upset, I always think it's bad design."
You might have got the wrong end of the stick - MERGE (SQL Server/ANSI standard version of UPSERT) is great for doing data imports when you'll have some inserts, some updates, and some deletes. Yes, you could write it as 3 statements (insert, update, delete), but sometimes just having it in a single statement can increase code clarity quite a bit by making intentions exceptionally obvious.
https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/
Postgres really has been coming along the last few years (i've been using it since version 7.1). I think UPSERT will really help it make new friends but some of the other tweaks may even lead to monkey dances: will the BRIN index makes Postgres suitable for time series work?
Still some things are never finished. What features are we missing from Postgres?
Personally, having recently being bitten by it, I'd love to see support for loose index scans be built into the optimiser.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2022
