Feedback-ID: i76984098:Fastmail
Message-ID: <22052a80-6461-4bc3-b75b-7b1063982101@aklaver.com>
Date: Wed, 24 Sep 2025 16:03:10 -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/22052a80-6461-4bc3-b75b-7b1063982101%40aklaver.com>
Precedence: bulk

On 9/24/25 10:02, Samuel Marks wrote:
> On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver 

> 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.
> 
> I would preference a single statement (one semicolon) solution; but for 
> now at least this works 🤷

I don't have enough experience with below to come up with an off the top 
of my head examples, but they look like they may offer alternatives.

MERGE:

https://www.postgresql.org/docs/current/sql-merge.html

and/or Row level Security:

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

In above link see example that starts below the phrase:

"... If it is necessary to consult other rows or other tables to make a 
policy decision, that can be accomplished using sub-SELECTs, or 
functions that contain SELECTs, in the policy expressions. ... "


-- 
Adrian Klaver
adrian.klaver@aklaver.com