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 1v1StW-008wqK-87 for pgsql-general@arkaria.postgresql.org; Wed, 24 Sep 2025 17:03:06 +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 1v1StT-00EgcX-Qh for pgsql-general@arkaria.postgresql.org; Wed, 24 Sep 2025 17:03:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1StT-00EgcO-Ep for pgsql-general@lists.postgresql.org; Wed, 24 Sep 2025 17:03:03 +0000 Received: from mail-lf1-x12d.google.com ([2a00:1450:4864:20::12d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1StP-002f8d-0b for pgsql-general@lists.postgresql.org; Wed, 24 Sep 2025 17:03:02 +0000 Received: by mail-lf1-x12d.google.com with SMTP id 2adb3069b0e04-57b35d221e2so482384e87.3 for ; Wed, 24 Sep 2025 10:03:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758733375; x=1759338175; 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=fInrDyUcL1cQUNbLh1AYPwlI7aDWyOACcNaJjG4+H3c=; b=B3FpFrAUsyxTNuQWe/xUsQHSrCx8REuWYFvKUmXZkw17QRMDXdrWA8V/9+SkqClU2a 4VB+sjDC9vNzsUf8WlmmMKQNlrAe27PkmujZgrSG7DsanRFMuHuOl5jE+1mBt7iPft4E m8SJqK5kgk74Ap28aoaYetZCmsDMCbzr+kjVYj2CUVvyue3PETRJ60WYMwFDVT+w5bTw 6Z+US4Ki1NpTC7mwMD5AfiOjn7EPFGs1ICfKygd6XRLt/cEp1ixAR1Q1RPYg/1KIiZ7m ITE9xzCian2lhMsHVWavKzAhFsV4o6TYawQblmUSYYwat3TnJB8NsrdgWnJvODw/or74 kzKQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758733375; x=1759338175; 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=fInrDyUcL1cQUNbLh1AYPwlI7aDWyOACcNaJjG4+H3c=; b=mLPV68DRjugxBN408FkTDbEdcJNJQvqqiWsLLhdkbEDmwgE7giYRP4gUB/Nrc00i3K 2L1EdQdb0sWvIRCAW7DMKI3fbPOmtLwvojMDxXz5wMnIdq0ao75WVCKbDm/7B/KOx+4L 7joMzURxxWqKEdxBu216aiNn9rit7PPpVS21S/Pjujvty4lKqrD2TBE7UIdAqJRPb+cp 7asF5Z43kRB9WXq/UVmHTq+AC21+XAJr6J/k2v62EI3tNibuzHqa1+RCOnUG2HvKVZjT U+wK78u6LyGAdPqWwpN1RDDmdmqqP8YUyNPcAVOObC9hHP6Y7FsqImkve5xJsPR5Al+l bNtA== X-Forwarded-Encrypted: i=1; AJvYcCUDbTiO6kwMfcKBJZNZr+Pt0pGjcrftc4OgeLMCPIo9Reu0yd69a23UFv22xLH99cxj+wj2oDrKYwv3IfFm@lists.postgresql.org X-Gm-Message-State: AOJu0Yztxuh1f/HNE3B5AFLD061L9hGWwPq9y7SMiGTO4NhyDO7BQGaw Io//JHEILtZ0hKeGzM9UP8iDDZY4UBVQwEtX2FzAErTelfYACq2+3GjiugT6ubrVgrZp6IGfxW9 IXVWiOIg6UYvfXeu63TgX0/UAn23fs1M= X-Gm-Gg: ASbGnct98BRH/t2w+0yynzccuImW4LECkTY9g3briJGH8sAsC9hAKQ2xRjYiCYSbaeo TY5/gxuYtYXL8tlkWMFdQI3+fzSNz64/ayTWmtEgQBm1Ld2VHIr0WktAJ3Ja/1GGv4vIShaXF9h muGCDZdTI1Ti12vAMH263KkhyJ29sJXDlrUKEXwVqgaK7g2cpy/THe47i8DAT8piz4F0ODSY/jX WCRroWL X-Google-Smtp-Source: AGHT+IGdvdySrocqtTbnoF3ZgAkl2OoIcZraZ621lBRb/DJxMBFekKrPiCc8U4Feq9pn/+It0aM2xgJrmYRbCt2z6zI= X-Received: by 2002:a2e:be9c:0:b0:336:8fa8:e02b with SMTP id 38308e7fff4ca-36f7f934683mr234921fa.5.1758733373600; Wed, 24 Sep 2025 10:02:53 -0700 (PDT) MIME-Version: 1.0 References: <8f2e4644-eccd-49e8-b70b-b5c51c3aaa8c@aklaver.com> In-Reply-To: <8f2e4644-eccd-49e8-b70b-b5c51c3aaa8c@aklaver.com> From: Samuel Marks Date: Wed, 24 Sep 2025 12:02:42 -0500 X-Gm-Features: AS18NWC-MvGhb-2mbYFLKRzwQo7isocXisSvBDHA32H4ikJaZr0fxOFtoyS9MPQ Message-ID: Subject: Re: How do I upsert depending on a second table? To: Adrian Klaver Cc: Juan Rodrigo Alejandro Burgos Mella , pgsql-general Content-Type: multipart/alternative; boundary="000000000000fcf7af063f8f0523" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000fcf7af063f8f0523 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Sep 24, 2025 at 10:13=E2=80=AFAM Adrian Klaver 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 =3D 'org0' > AND owner =3D 'wrong user'; > > will cause a divide by 0 error and abort the transaction preventing the > INSERT from happening. > > Example: > > test=3D# begin ; > BEGIN > test=3D*# select 1/0; > ERROR: division by zero > test=3D!# select 1; > ERROR: current transaction is aborted, commands ignored until end of > transaction block > test=3D!# 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 =F0=9F=A4=B7 > > > > 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; > > ``` --000000000000fcf7af063f8f0523 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Sep 24, 2025 at 10:13=E2=80=AFAM 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 o= rg
> WHERE name =3D 'org0'
> =C2=A0 =C2=A0AND owner =3D= 'wrong user';
>
> will cause a divide by 0 error and a= bort the transaction preventing the
> INSERT from happening.
><= br>> Example:
>
> test=3D# begin ;
> BEGIN
> tes= t=3D*# select 1/0;
> ERROR: =C2=A0division by zero
> test=3D!# = select 1;
> ERROR: =C2=A0current transaction is aborted, commands ign= ored until end of
> transaction block
> test=3D!# rollback ;> ROLLBACK
>
>
>

Yes but it's = meant to divide by zero. That cancels the whole transaction stopping it fro= m 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=C2=A0=F0=9F=A4=B7

> >=
> > El mar, 23 sept 2025, 23:19, Samuel Marks <samuelmarks@gmail.com
> > <mail= to:samuelmarks@gmail.com>&g= t; escribi=C3=B3:
> >
> > =C2=A0 =C2=A0 Ok so you're = thinking I give up on putting it all in one query and
> > =C2=A0 = =C2=A0 instead use a transaction? - Is that the recommended way?
> &g= t;
> > =C2=A0 =C2=A0 ```sql
> > =C2=A0 =C2=A0 TRUNCATE re= po, org;
> > =C2=A0 =C2=A0 INSERT INTO org(name, owner) VALUES (&#= 39;org0', 'user0');
> > =C2=A0 =C2=A0 ```
> >=
> > =C2=A0 =C2=A0 ```sql
> > =C2=A0 =C2=A0 START TRANSAC= TION READ WRITE;
> >
> > =C2=A0 =C2=A0 SELECT 1/COUNT(*)<= br>> > =C2=A0 =C2=A0 FROM org
> > =C2=A0 =C2=A0 WHERE name = =3D 'org0'
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0AND owner =3D &#= 39;wrong user';
> >
> > =C2=A0 =C2=A0 INSERT INTO rep= o (id, full_name, org)
> > =C2=A0 =C2=A0 VALUES (0, 'org0/name= 0 by wrong user', 'org0')
> > =C2=A0 =C2=A0 ON CONFLIC= T (full_name) DO UPDATE
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SET = full_name =3D EXCLUDED.full_name,
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0org =C2=A0 =C2=A0 =C2=A0 =3D EXCLUDED.org
> > = =C2=A0 =C2=A0 RETURNING id;
> >
> > =C2=A0 =C2=A0 COMMIT;=
> > =C2=A0 =C2=A0 ```
--000000000000fcf7af063f8f0523--