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

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
adrian(dot)klaver(at)aklaver(dot)com