Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres gets support for upsert

git.postgresql.org

Post reply on HN
Print view View original

61–70 of 114 posts

Re: Postgres gets support for upsert

#61

Post by ivoras »

Does the description seems like it's a two-pass operation to anyone else? Can anyone with more detailed knowledge tell why can't this be a single-pass "INSERT in a unique index, UPDATE if it fails" operation?

Re: Postgres gets support for upsert

#62

Post by qooleot »

threeseed wrote: post #58
slapresta wrote: post #50

Earlier quoted context omitted.

> And if MySQL wasn't robust then YouTube, Facebook, Twitter, Alibaba, LinkedIn etc wouldn't be using it for core parts of their infrastructure. It's definitely robust. Popularity is not an argument for quality. See: crocs, Justin Bieber, PHP. Those companies you mentioned, like many others, probably use it because they're locked in that technology, not because it's a superior one. Just like banks still use COBOL. If…

I never said popularity was an argument for quality. You did. What I am saying is that if MySQL wasn't robust then those companies simply wouldn't be using it. Since at their scale any bug or weakness will manifest at a level far greater than say at a startup. And they have the skills, time and money to choose any technology they wan't so I don't buy your argument that they are "locked in". Some like Facebook and Lin…

" never said popularity was an argument for quality. You did."

I'd tend to think your statement:

"And if MySQL wasn't robust then YouTube, Facebook, Twitter, Alibaba, LinkedIn etc wouldn't be using it for core parts of their infrastructure. It's definitely robust."

..points out that many (popular) sites use it, and a prerequisite is robustness, which thesaurus-wise, sounds a lot like quality.

I get that

a) you didn't actually say it, and

b) you could mean something much more specific, such as "companies with many highly-starred, complex open source projects which have also re-written major parts of their tech stack, but chose to leave mysql in place".

I know thats a bit more verbose, but it just seems to obviously close to the other author's interpretation with the ambiguity of the statement.

Re: Postgres gets support for upsert

#64

Post by daigoba66 »

viraptor wrote: post #19

I love that it's there now. I've been waiting for it for a long time, but one thing I don't get is... why does every single implementation have to have their own slightly different syntax? pgsql -> on conflict mysql -> replace / on duplicate oracle -> merge mssql -> merge sqlite -> insert or replace firebird -> merge / update or insert

I'm a fan of MERGE in T-SQL. An "upsert" is just one use-case enabled by the statement. Granted for the relatively simple use-case of an "upsert", especially a single row, MERGE has incredibly cumbersome syntax.

But MERGE also, in my opinion, aligns better with set-based logic. I.e., I have two _sets_ of data that I want to merge together. In some cases I want need to INSERT rows into the target, in other cases I need to UPDATE, and in some cases I might DELETE. The in T-SQL is also quite useful.

Re: Postgres gets support for upsert

#65

Post by spacemanmatt »

atombender wrote: post #28
colanderman wrote: post #11

Earlier quoted context omitted.

Just "using a transaction" is insufficient. You must be prepared to handle the case that neither the INSERT nor the UPDATE succeeds (with READ COMMITTED isolation), or that the transaction fails (with REPEATABLE READ isolation or better), by repeating the transaction. And if latency is at all a concern to you, you must wrap this all in a stored procedure to avoid the necessary round-trip-time between the commands. He…

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.

Re: Postgres gets support for upsert

#69

Post by leftnode »

kbendyk wrote: post #37

I 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.

Same thing here, I would love this feature. I like to make my tables have a common structure:

    id
    created_at
    updated_at
    status
    fkey1_id
    fkey2_id
    fkey3_id
    date_field1
    date_field2
    column1
    column2
    column3
Being able to add a new foreign key column with the "rest" of them would be awesome.
Post reply on HN