How do I upsert depending on a second table?

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 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 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ó:

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:

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ó:

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&gt; =

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

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 🤷

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