Feedback-ID: i76984098:Fastmail
Message-ID: <8f2e4644-eccd-49e8-b70b-b5c51c3aaa8c@aklaver.com>
Date: Wed, 24 Sep 2025 08:13:29 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How do I upsert depending on a second table?
To: Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>,
 Samuel Marks <samuelmarks@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAHbZ42zCptkYZi=QHaeDFHL9adPS1-nE6xoUC5QBa5VcF0dihA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/8f2e4644-eccd-49e8-b70b-b5c51c3aaa8c%40aklaver.com>
Precedence: bulk

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@gmail.com 
> <mailto:samuelmarks@gmail.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@gmail.com <mailto:rodrigoburgosmella@gmail.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@gmail.com <mailto: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 <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.klaver@aklaver.com