Subject: Re: How do I upsert depending on a second table?

From: Samuel Marks <[email-masked]>
Date: Tue, 23 Sep 2025 23:19:33 -0500

Ok so you're thinking I give up on putting it all in one query and
instead use a transaction? - Is that the recommended way?

```sql
TRUNCATE repo, org;
INSERT INTO org(name, owner) VALUES ('org0', 'user0');
```

```sql
START TRANSACTION READ WRITE;

SELECT 1/COUNT(*)
FROM org
WHERE name = 'org0'
  AND owner = 'wrong user';

INSERT INTO repo (id, full_name, org)
VALUES (0, 'org0/name0 by wrong user', 'org0')
ON CONFLICT (full_name) DO UPDATE
    SET full_name = EXCLUDED.full_name,
        org       = EXCLUDED.org
RETURNING id;

COMMIT;
```

On Tue, Sep 23, 2025 at 7:25 PM Juan Rodrigo Alejandro Burgos Mella
<[email-masked]> wrote:
>
> Hi Samuel
>
> Using ON CONFLICT is a headache.
> It's better to use the versatility of a Trigger: you have the full record at your fingertips, and if you're going to UPDATE, you have the previous record too.
> There's much more control.
>
> Also, you can always count on the beloved foreign keys, which are also quite useful.
>
> Atte.
> JRBM
>
>
> El mar, 23 sept 2025 a las 15:37, Samuel Marks (<[email-masked]>) escribió:
>>
>> Attempt:
>> ```sql
>> CREATE TABLE org
>> (
>>     "name"      VARCHAR(50) PRIMARY KEY,
>>     owner       VARCHAR(50) NOT NULL
>> );
>>
>> CREATE TABLE repo
>> (
>>     "id"           INTEGER PRIMARY KEY,
>>     full_name      VARCHAR(255) UNIQUE NOT NULL,
>>     org            VARCHAR(50)         NOT NULL REFERENCES org ("name")
>> );
>>
>> INSERT INTO org(name, owner) VALUES ('org0', 'user0');
>>
>> INSERT INTO repo (id, full_name, org)
>> VALUES (0, 'org0/name0 by wrong user', 'org0')
>> ON CONFLICT (full_name) DO UPDATE
>>     SET full_name = EXCLUDED.full_name,
>>         org       = EXCLUDED.org
>> WHERE EXISTS (SELECT 1
>>               FROM org org_tbl
>>               WHERE org_tbl.name = EXCLUDED.org
>>                 AND org_tbl.owner = 'wrong user')
>> RETURNING *;
>>
>> SELECT * FROM repo WHERE id = 0;
>> ```
>>
>> This all succeeds. It should fail because the 'wrong user' is trying
>> to create a new—or update an existing—repo.
>>
>> Thanks for all suggestions
>>
>>

---

From: Juan Rodrigo Alejandro Burgos Mella <[email-masked]>
Date: Wed, 24 Sep 2025 01:44:07 -0500

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

JRBm

El mar, 23 sept 2025, 23:19, Samuel Marks <[email-masked]> escribió:

> > It's better to use the versatility of a Trigger: you have the full
> record at your fingertips, and if you're going to UPDATE, you have the
> previous record too.

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 24 Sep 2025 08:13:29 -0700

On 9/23/25 23:44, Juan Rodrigo Alejandro Burgos Mella wrote:
> The insert works because there is no data in the repo table that 
> conflicts with the entered full name.

Except this part:

SELECT 1/COUNT(*)
FROM org
WHERE name = 'org0'

will cause a divide by 0 error and abort the transaction preventing the 
INSERT from happening.

Example:

test=# begin ;
BEGIN
test=*# select 1/0;
ERROR:  division by zero
test=!# select 1;
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
test=!# rollback ;
ROLLBACK

> 
> JRBm
> 
> El mar, 23 sept 2025, 23:19, Samuel Marks <[email-masked] 
> <mailto:[email-masked]>> escribió:
> 
>     <[email-masked] <mailto:[email-masked]>>
>     wrote:
>      >
>      > Hi Samuel
>      >
>      > It's better to use the versatility of a Trigger: you have the
>     full record at your fingertips, and if you're going to UPDATE, you
>     have the previous record too.
>      > El mar, 23 sept 2025 a las 15:37, Samuel Marks
>     (<[email-masked] <mailto:[email-masked]>>) escribió:
>      >>
>      >> Attempt:
>      >> ```sql
>      >> CREATE TABLE org
>      >> (
>      >>               WHERE org_tbl.name <http://org_tbl.name> =
>     EXCLUDED.org

-- 
Adrian Klaver
[email-masked]

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 24 Sep 2025 10:51:34 -0600

This thread is annoyingly full of replies that do not follow the
established conventions for making threads like this readable online and in
the archive.

Please:
1. Avoid top-posting and instead include your replies inline (or, at worse,
at the end)
2. Remove content not relevant to your immediate reply.
3. After you’ve made your last inline comment REMOVE all subsequent
content.  This is just a special case of point 2 but this last message
makes it extremely obvious just how obnoxious leaving trailing off-topic
content is.

David J.

---

From: Samuel Marks <[email-masked]>
Date: Wed, 24 Sep 2025 12:02:42 -0500

> ERROR:  current transaction is aborted, commands ignored until end of

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 🤷

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 24 Sep 2025 11:28:23 -0700

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

> that at point of update or insert [upsert] the org owner matches the 

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 🤷
>

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 24 Sep 2025 16:03:10 -0700

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

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 26 Sep 2025 15:17:02 -0700

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

First time working with MERGE, so approach the below with caution:

CREATE TABLE org
(

CREATE TABLE repo
(

WITH t AS (SELECT
     *
FROM
     org
RIGHT JOIN
     (values(0 , 'org0/name0 by wrong user', 'org0', 'wrong_user'))
         AS v(id, full_name, org, user_name)
      ON
         org.name = v.org
      AND
         org.owner = v.user_name
)
MERGE INTO repo as r
USING t
ON
     r.org = t.name

WHEN MATCHED AND t.id = r.id THEN
     UPDATE  SET (id, full_name, org) = (t.id, t.full_name, t.org)
WHEN NOT MATCHED AND t.user_name = COALESCE(t.owner, '') THEN
     INSERT VALUES(t.id, t.full_name, t.org)

RETURNING r.*;

  id | full_name | org
----+-----------+-----
(0 rows)

MERGE 0

select * from repo ;
  id | full_name | org
----+-----------+-----

WITH t AS (SELECT
     *
FROM
     org
RIGHT JOIN
     (values(0 , 'org0/name0 by right user', 'org0', 'user0'))
         AS v(id, full_name, org, user_name)
      ON
         org.name = v.org
      AND
         org.owner = v.user_name
)
MERGE INTO repo as r
USING t
ON
     r.org = t.name

WHEN MATCHED AND t.id = r.id THEN
     UPDATE  SET (id, full_name, org) = (t.id, t.full_name, t.org)
WHEN NOT MATCHED AND t.user_name = COALESCE(t.owner, '') THEN
     INSERT VALUES(t.id, t.full_name, t.org)

RETURNING r.*;
  id |        full_name         | org
----+--------------------------+------
   0 | org0/name0 by right user | org0
(1 row)

MERGE 1

select * from repo ;
  id |        full_name         | org
----+--------------------------+------
   0 | org0/name0 by right user | org0

WITH t AS (SELECT
     *
FROM
     org
RIGHT JOIN
     (values(0 , 'org0/name0 by right user update', 'org0', 'user0'))
         AS v(id, full_name, org, user_name)
      ON
         org.name = v.org
      AND
         org.owner = v.user_name
)
MERGE INTO repo as r
USING t
ON
     r.org = t.name

WHEN MATCHED AND t.id = r.id THEN
     UPDATE  SET (id, full_name, org) = (t.id, t.full_name, t.org)
WHEN NOT MATCHED AND t.user_name = COALESCE(t.owner, '') THEN
     INSERT VALUES(t.id, t.full_name, t.org)

RETURNING r.*;
  id |            full_name            | org
----+---------------------------------+------
   0 | org0/name0 by right user update | org0
(1 row)

select * from repo ;
  id |            full_name            | org
----+---------------------------------+------
   0 | org0/name0 by right user update | org0
(1 row)