public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Samuel Marks <[email protected]>
Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: How do I upsert depending on a second table?
Date: Wed, 24 Sep 2025 11:28:23 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMfPbcZeOZCdosvokifS=Kj6seakGFNvM6vM3v8FQNho7v_7Og@mail.gmail.com>
References: <CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com>
	<CAHbZ42xv5f-DrCnhrszKzmLrTQzrw1=3OYSyfEzM9rPVXAy_MQ@mail.gmail.com>
	<CAMfPbcbuww3geDUFce+eQ2DEtg1n=qHQ3N=2LdZYdJ55cUUHBA@mail.gmail.com>
	<CAHbZ42zCptkYZi=QHaeDFHL9adPS1-nE6xoUC5QBa5VcF0dihA@mail.gmail.com>
	<[email protected]>
	<CAMfPbcZeOZCdosvokifS=Kj6seakGFNvM6vM3v8FQNho7v_7Og@mail.gmail.com>

On 9/24/25 10:02, Samuel Marks wrote:
> On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver 
> <[email protected] <mailto:[email protected]>> wrote:

> 
> Yes but it's meant to divide by zero. That cancels the whole transaction 
> stopping it from going through. It being a transaction lets me guarantee 
> that at point of update or insert [upsert] the org owner matches the 
> requestor.

My reply was to Juan Rodrigo Alejandro Burgos Mella referencing the comment:

"The insert works because there is no data in the repo table that 
conflicts with the entered full name. "

I was pointing out that in your second example the INSERT would not 
happen as the org table does not have a row:

name 	owner
org0    wrong_user

So the SELECT 1/COUNT(*) [...] would result in a divide by 0 error and 
the transaction would abort. Therefore ON CONFLICT (full_name) DO UPDATE 
does not apply as the INSERT never happens.

I should have added previously this only applies for the 'wrong user' 
case. For cases where the correct name/owner exists in the org table 
then the INSERT and it's ON CONFLICT come into play and what happens 
then is dependent on whether there is an existing row in the repo with 
the same full_name or not. The issue I see is that the full_name is 
UNIQUE across all orgs and I not sure that is good idea. It would seem 
to me UNIQUE(org, full_name) would be better.

> 
> I would preference a single statement (one semicolon) solution; but for 
> now at least this works 🤷
> 




-- 
Adrian Klaver
[email protected]

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: How do I upsert depending on a second table?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox