Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1JEn-0070sl-7o for pgsql-general@arkaria.postgresql.org; Wed, 24 Sep 2025 06:44:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v1JEl-00AsLb-Tj for pgsql-general@arkaria.postgresql.org; Wed, 24 Sep 2025 06:44:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1JEl-00AsLT-FH for pgsql-general@lists.postgresql.org; Wed, 24 Sep 2025 06:44:23 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1JEj-0028Gd-0E for pgsql-general@lists.postgresql.org; Wed, 24 Sep 2025 06:44:22 +0000 Received: by mail-ej1-x636.google.com with SMTP id a640c23a62f3a-b30ead58e0cso226520866b.0 for ; Tue, 23 Sep 2025 23:44:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758696260; x=1759301060; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=yX8/LNOLFAg1ImTY3tIyBrA5qn6CrguYe//So+XKa2Q=; b=D8oDxCeRxy4VuoXDREc4WQFpnpAJwQ7X5/Iew8W8HJHBw/+7Bs1WaB3mPpF41uT8SW /gMRO5hniKIGoNczD0YivKwscZ/Ku48HYMYoY06hQ4D0ie+d7VeWEDYP1FeRH/YTMciJ YdGfoFv3SZB8mxpiLCsKfR+OhObmiPGFx5Na74RvG/pUk4/LtEGCRp6NnOzyLdhr0zJu WlpDDbG7Vr0rqo9A5XNLMDAOnJAy4Z7GFdlozjpZPrtLGzeh2YIxqKdjy3wRaETUBfoz Qk7h6Ou7sj+d1fR3C/3XkJ8Cm+5oJdR4H61GmPWKuatwwkIbQ/AygGrDiNjr68c2ESA4 RX5Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758696260; x=1759301060; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=yX8/LNOLFAg1ImTY3tIyBrA5qn6CrguYe//So+XKa2Q=; b=cCodiATkEOOgqFXfyn6LwEX2S1xGifBDjFOJ4871fbWEkMKyx1u4WAzD97gkMjn7Wo 8rJjdkADA5NTaoVzCXWWeV+h0vXfFwZxNzukMuAhL62V+wwauXpa9jqgrlFqDayrTkFR N8NGLblV7swF0aWYfRI5Rb3utOevJytId7IAr2k9ygpDDN0gdYuNdvA/X3CyAiYira6b +CsdWymQwVrAvyaIREp0VvaMZN9+2Y4aziykCgk81zPZfYWUkxOej3GpRm8ON9kQ7Vhg fQnPj8fvcEwc8XZByeTMvaJ41MfdDr8rK97jw5YHuGlzoDEoGnTUs+DkE5Nowz5PxbX/ UAjw== X-Gm-Message-State: AOJu0YwxiM8Mx1F25iYv9DyFznSQVENL1tTmzOqeerzNBxX/vlHZxyIs lwC18dFAzx9A58cu5AvNdheIb0sI5q2kGjNHIAGCmhgrnjml+oAvYTfL1oHBpOJ32q3Qy44aLTp wlGnJPnXZq+OcR6vz0Hj79KIsR7VcDcLn0Xs= X-Gm-Gg: ASbGncshbH+l6gx9McMVJQ9pJFgPrAx84RxFSxWUkGQZL/wr3M91sNoEpegXYzvmoau UWoSVdZU81zPQqG79l+QQzmGbdZsVVWdO1Jg4lpwfn646sv3SPCVSzAV6Ep+o+q2praXXTTiyWB HXj/XaBTKvzA/uXknn+KCzfISN39WERZ9NOc/1h34rCHMPaEHLhk4XmBGEF4vl7/F9txJ1dn/gm 8Mk X-Google-Smtp-Source: AGHT+IFvo/QRkq4nIOyIF3IIgdD9hL1JPWtffFeu09lUsrWgbSjB9aS3ZABsMextdFsqQqxa7gyWor0gAcwHAUhBiSg= X-Received: by 2002:a17:907:3f20:b0:b2e:34f1:9dbf with SMTP id a640c23a62f3a-b30261c1e6amr489675466b.1.1758696259732; Tue, 23 Sep 2025 23:44:19 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Juan Rodrigo Alejandro Burgos Mella Date: Wed, 24 Sep 2025 01:44:07 -0500 X-Gm-Features: AS18NWA0VPt-8_ES2S2Otmyd4hhfHGzThqj-09RxQrDgChhfjcnfq5yMC7EmnZ8 Message-ID: Subject: Re: How do I upsert depending on a second table? To: Samuel Marks Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000d446d1063f86611c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d446d1063f86611c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 escribi= =C3=B3: > 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 =3D 'org0' > AND owner =3D '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 =3D EXCLUDED.full_name, > org =3D EXCLUDED.org > RETURNING id; > > COMMIT; > ``` > > > > On Tue, Sep 23, 2025 at 7:25=E2=80=AFPM Juan Rodrigo Alejandro Burgos Mel= la > 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 (= ) > 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 > >> > >> > --000000000000d446d1063f86611c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
The insert works because there is no data in the repo tab= le that conflicts with the entered full name.

JRBm

El mar, 23 sept 2025, 23:19= , Samuel Marks <samuelmarks@gma= il.com> escribi=C3=B3:
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 =3D 'org0'
=C2=A0 AND owner =3D 'wrong user';

INSERT INTO repo (id, full_name, org)
VALUES (0, 'org0/name0 by wrong user', 'org0')
ON CONFLICT (full_name) DO UPDATE
=C2=A0 =C2=A0 SET full_name =3D EXCLUDED.full_name,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 org=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D EXCLUDED.org<= br> RETURNING id;

COMMIT;
```



On Tue, Sep 23, 2025 at 7:25=E2=80=AFPM 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@gma= il.com>) escribi=C3=B3:
>>
>> Attempt:
>> ```sql
>> CREATE TABLE org
>> (
>>=C2=A0 =C2=A0 =C2=A0"name"=C2=A0 =C2=A0 =C2=A0 VARCHAR(50= ) PRIMARY KEY,
>>=C2=A0 =C2=A0 =C2=A0owner=C2=A0 =C2=A0 =C2=A0 =C2=A0VARCHAR(50) NOT= NULL
>> );
>>
>> CREATE TABLE repo
>> (
>>=C2=A0 =C2=A0 =C2=A0"id"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0INTEGER PRIMARY KEY,
>>=C2=A0 =C2=A0 =C2=A0full_name=C2=A0 =C2=A0 =C2=A0 VARCHAR(255) UNIQ= UE NOT NULL,
>>=C2=A0 =C2=A0 =C2=A0org=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 VA= RCHAR(50)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0NOT NULL REFERENCES org ("n= ame")
>> );
>>
>> INSERT INTO org(name, owner) VALUES ('org0', 'user0= 9;);
>>
>> INSERT INTO repo (id, full_name, org)
>> VALUES (0, 'org0/name0 by wrong user', 'org0')
>> ON CONFLICT (full_name) DO UPDATE
>>=C2=A0 =C2=A0 =C2=A0SET full_name =3D EXCLUDED.full_name,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0org=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D= EXCLUDED.org
>> WHERE EXISTS (SELECT 1
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM org org= _tbl
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE o= rg_tbl.name =3D EXCLUDED.org
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND o= rg_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
>>
>>
--000000000000d446d1063f86611c--