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

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 <samuelmarks(at)gmail(dot)com> escribió:

> 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
> <rodrigoburgosmella(at)gmail(dot)com> 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 (<samuelmarks(at)gmail(dot)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
> >>
> >>
>