Feedback-ID: i76984098:Fastmail
Message-ID: <c1dff98c-0902-4582-8bea-7241ae86486f@aklaver.com>
Date: Wed, 24 Sep 2025 11:28:23 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How do I upsert depending on a second table?
To: Samuel Marks <samuelmarks@gmail.com>
Cc: Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
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>
 <8f2e4644-eccd-49e8-b70b-b5c51c3aaa8c@aklaver.com>
 <CAMfPbcZeOZCdosvokifS=Kj6seakGFNvM6vM3v8FQNho7v_7Og@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAMfPbcZeOZCdosvokifS=Kj6seakGFNvM6vM3v8FQNho7v_7Og@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/c1dff98c-0902-4582-8bea-7241ae86486f%40aklaver.com>
Precedence: bulk

On 9/24/25 10:02, Samuel Marks wrote:
> On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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
adrian.klaver@aklaver.com