public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Samuel Marks <[email protected]>
To: [email protected]
Subject: Re: How do I upsert depending on a second table?
Date: Tue, 23 Sep 2025 13:57:53 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com>
References: <CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com>

On 9/23/25 13:36, Samuel Marks wrote:
> 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;
> ```

Also, as shown, there is no conflict so I don't see the condition being 
run per:

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

"
condition

     An expression that returns a value of type boolean. Only rows for 
which this expression returns true will be updated, although all rows 
will be locked when the ON CONFLICT DO UPDATE action is taken. Note that 
condition is evaluated last, after a conflict has been identified as a 
candidate to update.

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


-- 
Adrian Klaver
[email protected]

view thread (2+ 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]
  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