The Heap™ – Consultancy ©®

Q: INSERT ON CONFLICT DO NOTHING efficiency (PostgreSQL)

7:01 AM

A chairde - Morning all!

McNets

Morning

Colin 't Hart

7:23 AM

Morning

Q: Revisiting upsert vs merge

Back in 2013 we decided that upsert and merge are two different things and questions about such should be tagged separately. Today Postgres has insert ... on conflict, and upsert has mostly historical Postgres questions, while new questions about "insert ... on conflict" are for the most part not...

promtped by this IMHO good question:

In Postgres, is it inefficient to perform an “INSERT… ON CONFLICT DO NOTHING” if >99.9% of the time the record exists and nothing is done? E.g. let’s say I have a Node process which fetches 100k records which map to rows, of which 99,980 already exist. I could: Insert them all, but do nothing on...

postgresql postgresql-performance

1 hour later…

Aditya Vyas - Lakhan

8:34 AM

hi is there anyone works on postgresql

@Colin'tHart - I'm not being smart, but can't the OP test that one for themselves? I mean the answer could vary depending on the percentage of records to be updated? I don't know the answer BTW.

8:56 AM

Gave my answer to your meta-question - +1

Colin 't Hart

@AdityaVyas-Lakhan Yes, lots of people use Postgres.

@Vérace yes but the question says they wonder about their case, where the actual update percentage is very small, 0.02%

Aditya Vyas - Lakhan

9:17 AM

@Colin'tHart I am facing one issue in one of my project, how to show my code ?

@ypercubeᵀᴹ - I agree - I upvoted the question! I'll just ask for a bit more detail!

@AdityaVyas-Lakhan You can ask a question on the site and put the code there. Additionally you can use dbfiddle.uk/?rdbms=postgres_13 and add a link to your code in the question.

@PaulWhite the "parent user" link is a dead end for this one: chat.stackexchange.com/users/138137/aditya-vyas-lakhan Why?

2 hours later…

Michael Green

11:58 AM

Some context for the earlier post theguardian.com/australia-news/2021/sep/16/…

heh, I was wondering about that!

Paul White

@ypercubeᵀᴹ No idea. Maybe they deleted their SO account, or chat is out of sync in some way.

I refreshed it and it seems to be ok now

@Vérace you upvoted and asked for clarity? Seems like an odd choice of actions.

Peter Vandivier

@PaulWhite sounds like an AG problem

@MichaelGreen it's no "Tim Apple" but it's still funny XP

Paul White

12:36 PM

@PeterVandivier Most things are

1:01 PM

@HannahVernon - I thought the question was a good one on its own merits - good enough for an upvote. I think it might be made better by the addition of the information I requested, but hey, who's perfect?

1:51 PM

@HannahVernon - just put in the last update to that question as you suggested - any more and it'll become a doctoral thesis :-) As a final word, I also added a bit about SQL style...

A J

2:02 PM

@PaulWhite NZ won the toss and opted to go home. Nice! ;)

I knew that series is going to be cancelled.

Cade Roux

2:20 PM

Morning

@Vérace awesome

1 hour later…

3:25 PM

Well, I'm now officially off my strike! I will be revisiting the review queues since (my honour), err... order has been restored as have my previous reviews on first posts. Lesson to be learned by SE - guys, WTF (the "W" in this case is "WHY") can't you consult with your users before doing crap? I know that some people will not be pleased by any change! Aesop's fable "Try to please everyone, you'll end up pleasing no one!".

Would it be too much to suggest the application of a concept that's been around for a while now - DEE-MAH-KRAH-CEE - or at least having a vote (and then (DPRK style) ignoring it)? Or are we being ruled by the database Thought Police? :-)

6 hours later…

9:09 PM

just figured out that I've visited dba.se over 92.5% of the days since August 2012. I wonder if that makes me addicted.

1 hour later…

10:10 PM

@Vérace are you referring to something apecific?

10:43 PM

@HannahVernon can mods see the calendar?