back to article 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 …

  1. Nod

    Is it bad design

    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. Is the temptation of porting mysqls' ease of use a good thing?

  2. Nod

    Re: Is it bad design

    I meant upsert instead of upset, damn autocorrect

  3. Martin Gregorie Silver badge

    Re: Is it bad design

    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.

  4. tokyo-octopus

    Re: Is it bad design

    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.

  5. tokyo-octopus

    Re: Is it bad design

    Belay that, MySQL's MERGE thingy is a storage engine, they do have a similar-ish INSERT ... ON DUPLICATE KEY UPDATE. With the usual set of caveats about AUTO_INCREMENTS etcetera.

  6. Charlie Clark Silver badge

    Re: Is it bad design

    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.

  7. Anonymous Coward
    Anonymous Coward

    Re: Is it bad design

    > "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/

  8. Charlie Clark Silver badge
    Go

    Three cheers!

    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.

  9. Stevie Silver badge

    Bah!

    Isn't this another flavor of the O'racle MERGE statement?

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Biting the hand that feeds IT © 1998–2018