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

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 (<samuelmarks@gmail.com>)
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
>
>
>