public inbox for [email protected]
help / color / mirror / Atom feedRe: How do I upsert depending on a second table?
8+ messages / 4 participants
[nested] [flat]
* Re: How do I upsert depending on a second table?
@ 2025-09-24 04:19 Samuel Marks <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Samuel Marks @ 2025-09-24 04:19 UTC (permalink / raw)
To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; +Cc: [email protected]
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]> 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]>) 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
>>
>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How do I upsert depending on a second table?
@ 2025-09-24 06:44 Juan Rodrigo Alejandro Burgos Mella <[email protected]>
parent: Samuel Marks <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Juan Rodrigo Alejandro Burgos Mella @ 2025-09-24 06:44 UTC (permalink / raw)
To: Samuel Marks <[email protected]>; +Cc: pgsql-general <[email protected]>
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 <[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]> 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]>)
> 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
> >>
> >>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How do I upsert depending on a second table?
@ 2025-09-24 15:13 Adrian Klaver <[email protected]>
parent: Juan Rodrigo Alejandro Burgos Mella <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Adrian Klaver @ 2025-09-24 15:13 UTC (permalink / raw)
To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; Samuel Marks <[email protected]>; +Cc: pgsql-general <[email protected]>
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]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How do I upsert depending on a second table?
@ 2025-09-24 16:51 David G. Johnston <[email protected]>
parent: Adrian Klaver <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2025-09-24 16:51 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; Samuel Marks <[email protected]>; pgsql-general <[email protected]>
This thread is annoyingly full of replies that do not follow the
established conventions for making threads like this readable online and in
the archive.
Please:
1. Avoid top-posting and instead include your replies inline (or, at worse,
at the end)
2. Remove content not relevant to your immediate reply.
3. After you’ve made your last inline comment REMOVE all subsequent
content. This is just a special case of point 2 but this last message
makes it extremely obvious just how obnoxious leaving trailing off-topic
content is.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How do I upsert depending on a second table?
@ 2025-09-24 17:02 Samuel Marks <[email protected]>
parent: Adrian Klaver <[email protected]>
1 sibling, 2 replies; 8+ messages in thread
From: Samuel Marks @ 2025-09-24 17:02 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; pgsql-general <[email protected]>
On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver <[email protected]>
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 <[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;
> > ```
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How do I upsert depending on a second table?
@ 2025-09-24 18:28 Adrian Klaver <[email protected]>
parent: Samuel Marks <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2025-09-24 18:28 UTC (permalink / raw)
To: Samuel Marks <[email protected]>; +Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; pgsql-general <[email protected]>
On 9/24/25 10:02, Samuel Marks wrote:
> On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver
> <[email protected] <mailto:[email protected]>> wrote:
>
> 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.
My reply was to Juan Rodrigo Alejandro Burgos Mella referencing the comment:
"The insert works because there is no data in the repo table that
conflicts with the entered full name. "
I was pointing out that in your second example the INSERT would not
happen as the org table does not have a row:
name owner
org0 wrong_user
So the SELECT 1/COUNT(*) [...] would result in a divide by 0 error and
the transaction would abort. Therefore ON CONFLICT (full_name) DO UPDATE
does not apply as the INSERT never happens.
I should have added previously this only applies for the 'wrong user'
case. For cases where the correct name/owner exists in the org table
then the INSERT and it's ON CONFLICT come into play and what happens
then is dependent on whether there is an existing row in the repo with
the same full_name or not. The issue I see is that the full_name is
UNIQUE across all orgs and I not sure that is good idea. It would seem
to me UNIQUE(org, full_name) would be better.
>
> I would preference a single statement (one semicolon) solution; but for
> now at least this works 🤷
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How do I upsert depending on a second table?
@ 2025-09-24 23:03 Adrian Klaver <[email protected]>
parent: Samuel Marks <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2025-09-24 23:03 UTC (permalink / raw)
To: Samuel Marks <[email protected]>; +Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; pgsql-general <[email protected]>
On 9/24/25 10:02, Samuel Marks wrote:
> On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver
> 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 🤷
I don't have enough experience with below to come up with an off the top
of my head examples, but they look like they may offer alternatives.
MERGE:
https://www.postgresql.org/docs/current/sql-merge.html
and/or Row level Security:
https://www.postgresql.org/docs/current/ddl-rowsecurity.html
In above link see example that starts below the phrase:
"... If it is necessary to consult other rows or other tables to make a
policy decision, that can be accomplished using sub-SELECTs, or
functions that contain SELECTs, in the policy expressions. ... "
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How do I upsert depending on a second table?
@ 2025-09-26 22:17 Adrian Klaver <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2025-09-26 22:17 UTC (permalink / raw)
To: Samuel Marks <[email protected]>; +Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; pgsql-general <[email protected]>
On 9/24/25 16:03, Adrian Klaver wrote:
> On 9/24/25 10:02, Samuel Marks wrote:
>> On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver
> I don't have enough experience with below to come up with an off the top
> of my head examples, but they look like they may offer alternatives.
>
> MERGE:
>
> https://www.postgresql.org/docs/current/sql-merge.html
>
First time working with MERGE, so approach the below with caution:
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');
WITH t AS (SELECT
*
FROM
org
RIGHT JOIN
(values(0 , 'org0/name0 by wrong user', 'org0', 'wrong_user'))
AS v(id, full_name, org, user_name)
ON
org.name = v.org
AND
org.owner = v.user_name
)
MERGE INTO repo as r
USING t
ON
r.org = t.name
WHEN MATCHED AND t.id = r.id THEN
UPDATE SET (id, full_name, org) = (t.id, t.full_name, t.org)
WHEN NOT MATCHED AND t.user_name = COALESCE(t.owner, '') THEN
INSERT VALUES(t.id, t.full_name, t.org)
RETURNING r.*;
id | full_name | org
----+-----------+-----
(0 rows)
MERGE 0
select * from repo ;
id | full_name | org
----+-----------+-----
WITH t AS (SELECT
*
FROM
org
RIGHT JOIN
(values(0 , 'org0/name0 by right user', 'org0', 'user0'))
AS v(id, full_name, org, user_name)
ON
org.name = v.org
AND
org.owner = v.user_name
)
MERGE INTO repo as r
USING t
ON
r.org = t.name
WHEN MATCHED AND t.id = r.id THEN
UPDATE SET (id, full_name, org) = (t.id, t.full_name, t.org)
WHEN NOT MATCHED AND t.user_name = COALESCE(t.owner, '') THEN
INSERT VALUES(t.id, t.full_name, t.org)
RETURNING r.*;
id | full_name | org
----+--------------------------+------
0 | org0/name0 by right user | org0
(1 row)
MERGE 1
select * from repo ;
id | full_name | org
----+--------------------------+------
0 | org0/name0 by right user | org0
WITH t AS (SELECT
*
FROM
org
RIGHT JOIN
(values(0 , 'org0/name0 by right user update', 'org0', 'user0'))
AS v(id, full_name, org, user_name)
ON
org.name = v.org
AND
org.owner = v.user_name
)
MERGE INTO repo as r
USING t
ON
r.org = t.name
WHEN MATCHED AND t.id = r.id THEN
UPDATE SET (id, full_name, org) = (t.id, t.full_name, t.org)
WHEN NOT MATCHED AND t.user_name = COALESCE(t.owner, '') THEN
INSERT VALUES(t.id, t.full_name, t.org)
RETURNING r.*;
id | full_name | org
----+---------------------------------+------
0 | org0/name0 by right user update | org0
(1 row)
select * from repo ;
id | full_name | org
----+---------------------------------+------
0 | org0/name0 by right user update | org0
(1 row)
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-09-26 22:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-24 04:19 Re: How do I upsert depending on a second table? Samuel Marks <[email protected]>
2025-09-24 06:44 ` Juan Rodrigo Alejandro Burgos Mella <[email protected]>
2025-09-24 15:13 ` Adrian Klaver <[email protected]>
2025-09-24 16:51 ` David G. Johnston <[email protected]>
2025-09-24 17:02 ` Samuel Marks <[email protected]>
2025-09-24 18:28 ` Adrian Klaver <[email protected]>
2025-09-24 23:03 ` Adrian Klaver <[email protected]>
2025-09-26 22:17 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox