MIME-Version: 1.0
References: <CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com>
In-Reply-To: <CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com>
From: Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>
Date: Tue, 23 Sep 2025 19:25:10 -0500
Message-ID: <CAHbZ42xv5f-DrCnhrszKzmLrTQzrw1=3OYSyfEzM9rPVXAy_MQ@mail.gmail.com>
Subject: Re: How do I upsert depending on a second table?
To: Samuel Marks <samuelmarks@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a870be063f81163a"
Archived-At: <https://www.postgresql.org/message-id/CAHbZ42xv5f-DrCnhrszKzmLrTQzrw1%3D3OYSyfEzM9rPVXAy_MQ%40mail.gmail.com>
Precedence: bulk

--000000000000a870be063f81163a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=C3=B3:

> 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 =3D EXCLUDED.full_name,
>         org       =3D EXCLUDED.org
> WHERE EXISTS (SELECT 1
>               FROM org org_tbl
>               WHERE org_tbl.name =3D EXCLUDED.org
>                 AND org_tbl.owner =3D 'wrong user')
> RETURNING *;
>
> SELECT * FROM repo WHERE id =3D 0;
> ```
>
> This all succeeds. It should fail because the 'wrong user' is trying
> to create a new=E2=80=94or update an existing=E2=80=94repo.
>
> Thanks for all suggestions
>
>
>

--000000000000a870be063f81163a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Samuel</div><div><br></div><div>Using ON CONFLICT =
is a headache.=C2=A0</div><div>It&#39;s better to use the versatility of a =
Trigger: you have the full record at your fingertips, and if you&#39;re goi=
ng to UPDATE, you have the previous record too.=C2=A0</div><div>There&#39;s=
 much more control.</div><div><br></div><div>Also, you can always count on =
the beloved foreign keys, which are also quite useful.</div><div><br></div>=
<div>Atte.</div><div>JRBM</div><div><br></div></div><br><div class=3D"gmail=
_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">El mar,=
 23 sept 2025 a las 15:37, Samuel Marks (&lt;<a href=3D"mailto:samuelmarks@=
gmail.com">samuelmarks@gmail.com</a>&gt;) escribi=C3=B3:<br></div><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex">Attempt:<br>
```sql<br>
CREATE TABLE org<br>
(<br>
=C2=A0 =C2=A0 &quot;name&quot;=C2=A0 =C2=A0 =C2=A0 VARCHAR(50) PRIMARY KEY,=
<br>
=C2=A0 =C2=A0 owner=C2=A0 =C2=A0 =C2=A0 =C2=A0VARCHAR(50) NOT NULL<br>
);<br>
<br>
CREATE TABLE repo<br>
(<br>
=C2=A0 =C2=A0 &quot;id&quot;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INTEGE=
R PRIMARY KEY,<br>
=C2=A0 =C2=A0 full_name=C2=A0 =C2=A0 =C2=A0 VARCHAR(255) UNIQUE NOT NULL,<b=
r>
=C2=A0 =C2=A0 org=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 VARCHAR(50)=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0NOT NULL REFERENCES org (&quot;name&quot;)<b=
r>
);<br>
<br>
INSERT INTO org(name, owner) VALUES (&#39;org0&#39;, &#39;user0&#39;);<br>
<br>
INSERT INTO repo (id, full_name, org)<br>
VALUES (0, &#39;org0/name0 by wrong user&#39;, &#39;org0&#39;)<br>
ON CONFLICT (full_name) DO UPDATE<br>
=C2=A0 =C2=A0 SET full_name =3D EXCLUDED.full_name,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 org=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D EXCLUDED.org<=
br>
WHERE EXISTS (SELECT 1<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM org org_tbl<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE <a href=3D"http://or=
g_tbl.name" rel=3D"noreferrer" target=3D"_blank">org_tbl.name</a> =3D EXCLU=
DED.org<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND org_tbl.owner =
=3D &#39;wrong user&#39;)<br>
RETURNING *;<br>
<br>
SELECT * FROM repo WHERE id =3D 0;<br>
```<br>
<br>
This all succeeds. It should fail because the &#39;wrong user&#39; is tryin=
g<br>
to create a new=E2=80=94or update an existing=E2=80=94repo.<br>
<br>
Thanks for all suggestions<br>
<br>
<br>
</blockquote></div>

--000000000000a870be063f81163a--