Обсуждение: How do I upsert depending on a second table?
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
On 9/23/25 13:36, Samuel Marks wrote:
> 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')
Where is org_tbl?
Or is this a copy and paste error?
> 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
the AS syntax can alternatively be used for aliases https://www.postgresql.org/docs/current/sql-select.html `SELECT actual_tablename table0 WHERE table0.column00 = 1` (I used a space) On Tue, Sep 23, 2025 at 3:52 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 9/23/25 13:36, Samuel Marks wrote: > > 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') > > Where is org_tbl? > > Or is this a copy and paste error? > > > 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
On 9/23/25 13:36, Samuel Marks wrote:
> 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;
> ```
Also, as shown, there is no conflict so I don't see the condition being
run per:
https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
"
condition
An expression that returns a value of type boolean. Only rows for
which this expression returns true will be updated, although all rows
will be locked when the ON CONFLICT DO UPDATE action is taken. Note that
condition is evaluated last, after a conflict has been identified as a
candidate to update.
"
>
> 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
On 9/23/25 13:56, Samuel Marks wrote: > the AS syntax can alternatively be used for aliases > https://www.postgresql.org/docs/current/sql-select.html > > `SELECT actual_tablename table0 WHERE table0.column00 = 1` Ok, I missed the: ... FROM org org_tbl ... > > (I used a space) Yeah, I use tbl_name AS alias_name to help these old eyes catch this sort of thing in my queries. -- Adrian Klaver adrian.klaver@aklaver.com
Yeah I know my approach doesn't work, my question is, what is the
correct way to do an upsert for this schema?
Specifically:
- Create a new repo if one by that name doesn't exist + requestor is
`owner` of associated `org`
- Update an existing repo if one by that name does exist + requestor
is `owner` of associated `org`
On Tue, Sep 23, 2025 at 3:57 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 9/23/25 13:36, Samuel Marks wrote:
> > 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;
> > ```
>
> Also, as shown, there is no conflict so I don't see the condition being
> run per:
>
> https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
>
> "
> condition
>
> An expression that returns a value of type boolean. Only rows for
> which this expression returns true will be updated, although all rows
> will be locked when the ON CONFLICT DO UPDATE action is taken. Note that
> condition is evaluated last, after a conflict has been identified as a
> candidate to update.
>
> "
> >
> > 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
On Tuesday, September 23, 2025, Samuel Marks <samuelmarks@gmail.com> wrote:
$subject
You can only upsert/provoke a meaningful conflict on the singular table being inserted into.
There are other features like functions and triggers that may get you something usable.
David J.
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>) 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
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> 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>) 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
>>
>>
On 9/23/25 17:25, Juan Rodrigo Alejandro Burgos Mella wrote: > Hi Samuel > > Using ON CONFLICT is a headache. Like any tool ON CONFLICT has usage it is best for, if you try to force it do something it was not designed for then it will not perform as expected. Stick to what it good at and it will not be a headache. It is good at moving data into a table where the incoming data is a mix of entirely new rows and changes to existing rows for which there is some sort of arbiter to decide on whether there is a conflict or not. > 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. https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT " The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to the row proposed for insertion using the special excluded table. " > There's much more control. > > Also, you can always count on the beloved foreign keys, which are also > quite useful. > > Atte. > JRBM -- Adrian Klaver adrian.klaver@aklaver.com
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 <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> 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>) 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
>>
>>
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
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.
On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver <adrian.klaver@aklaver.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
>
>
> > <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 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@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 9/24/25 10:02, Samuel Marks wrote: > On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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 adrian.klaver@aklaver.com
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 adrian.klaver@aklaver.com
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 adrian.klaver@aklaver.com