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

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'
AND owner = 'wrong user';

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 <samuelmarks(at)gmail(dot)com
> <mailto: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 <mailto: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 <mailto: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 <http://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
> >>
> >>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com