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

On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
>
> 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
>
>
>

Yes but it's meant to divide by zero. That cancels the whole transaction
stopping it from going through. It being a transaction lets me guarantee
that at point of update or insert [upsert] the org owner matches the
requestor.

I would preference a single statement (one semicolon) solution; but for now
at least this works 🤷

> >
> > 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;
> > ```