public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>
To: Samuel Marks <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: How do I upsert depending on a second table?
Date: Wed, 24 Sep 2025 08:13:29 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHbZ42zCptkYZi=QHaeDFHL9adPS1-nE6xoUC5QBa5VcF0dihA@mail.gmail.com>
References: <CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com>
<CAHbZ42xv5f-DrCnhrszKzmLrTQzrw1=3OYSyfEzM9rPVXAy_MQ@mail.gmail.com>
<CAMfPbcbuww3geDUFce+eQ2DEtg1n=qHQ3N=2LdZYdJ55cUUHBA@mail.gmail.com>
<CAHbZ42zCptkYZi=QHaeDFHL9adPS1-nE6xoUC5QBa5VcF0dihA@mail.gmail.com>
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 <[email protected]
> <mailto:[email protected]>> 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
> <[email protected] <mailto:[email protected]>>
> 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
> (<[email protected] <mailto:[email protected]>>) 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
[email protected]
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: How do I upsert depending on a second table?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox