Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1plBWA-0005ac-Pe for pgsql-general@arkaria.postgresql.org; Sat, 08 Apr 2023 16:34:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1plBW9-0000KT-Mj for pgsql-general@arkaria.postgresql.org; Sat, 08 Apr 2023 16:34:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkato-0006OT-0t for pgsql-general@lists.postgresql.org; Fri, 07 Apr 2023 01:28:20 +0000 Received: from mg-aaus-pogs.mailguard.com.au ([3.24.56.135]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pkati-001Lm1-Jd for pgsql-general@lists.postgresql.org; Fri, 07 Apr 2023 01:28:19 +0000 Received: from mg-aaus-pogs.mailguard.com.au (localhost [127.0.0.1]) by mg-aaus-pogs.mailguard.com.au (Postfix) with ESMTP id 05E7B1204F0A; Fri, 7 Apr 2023 11:28:10 +1000 (AEST) Received: from AUS01-ME3-obe.outbound.protection.outlook.com (mail-me3aus01lp2237.outbound.protection.outlook.com [104.47.71.237]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mg-aaus-pogs.mailguard.com.au (Postfix) with ESMTPS id 3DCBE1204F3F; Fri, 7 Apr 2023 11:28:04 +1000 (AEST) ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=AYssWDo7dYYsvR6I59aG0mjQbrWeoC5cWPOdRM97fwAuQ9kL0s1Z87S/lVe4qnov89Phc/tBoEmO8VnhNKE5Zkx9eVe74W/g99BVgB1OI7kK1XM236WN/PV7rRE4cVq9NnB+vGi+qzpGJR0YR2P/cnRD46jcYURPBBD5EiHBJLP2c4JByJOv5x1QfXSv1FiczFLKm2CfkNQ/JQO2kq9ubMeF9aUDAyqb1n0mR8eoMNZ6q8R+bPx728rQAZ3puxY7vGwbxIjkZBSFArhEOHjrVtYqhnBujfvhBTnfi9dBGvbObUi7HOqrhFvr49CjVCyYTRGImrhSjaO4f4HfcbwFmQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=UUP8rbaFCwIzR8zPndnF/UuAeqx4GJfp02FBnOfKa8k=; b=Z99EmTpjcGtzQhjDvZhCn1LOk1pTFdr9aT2MWSQ29TFXapa9EDjLrSzlCTzldbGhcQYJw26lOr4ubCvKK1NYbT2k+YxlYasyhxhNm7XUq+xp6aGS6+KfWteKTalPFDlMI8/HjacElm89g6k8mRHaUbNgEJ61mXa+FkOsJCYw5AHRUBCw/Emuh5dz2KBOBcwLz949MWVeNBn52oBoTfNhCP8QHDQf+C34w2swLaO9N55/e/0WiPwU7uLnRceEmN03OdgP9/nPLmEujVxy6mftNNJjrwxQjd53kaNBHUxKh9ZI6TDbeTo4XDDS9lXJ3ASiaTJyXMn+U9/QE71ln1V3HQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=aquamonix.com.au; dmarc=pass action=none header.from=aquamonix.com.au; dkim=pass header.d=aquamonix.com.au; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aquamonix.com.au; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=UUP8rbaFCwIzR8zPndnF/UuAeqx4GJfp02FBnOfKa8k=; b=Q+4PQrdx2R793HVJdlhDyvIVwWwXOIuNnvtOHcxZk7sa70H54Tm7jttr6cwzmHomQT3xONZxgL97jD2chOV8XfRtgA28rFUKTB0xnlEfNJuoKfhQVWIp1LsqZE4ppimij4jCqTbTTF0IQ0Ikci92uavEjUV8BS7OP0ZiJtaIbD4= Received: from SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM (2603:10c6:10:a3::11) by ME3P282MB3017.AUSP282.PROD.OUTLOOK.COM (2603:10c6:220:13f::6) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6298.14; Fri, 7 Apr 2023 01:27:59 +0000 Received: from SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM ([fe80::39b6:baa7:9aca:b19e]) by SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM ([fe80::39b6:baa7:9aca:b19e%3]) with mapi id 15.20.6277.016; Fri, 7 Apr 2023 01:27:59 +0000 From: Louis Tian To: Adrian Klaver , "pgsql-general@lists.postgresql.org" Subject: Re: [EXTERNAL]: Re: UPSERT in Postgres Thread-Topic: [EXTERNAL]: Re: UPSERT in Postgres Thread-Index: AQHZaD++yYDuSAz8jEeE5AAohZ2RTq8exUMAgABAASk= Date: Fri, 7 Apr 2023 01:27:59 +0000 Message-ID: References: <66a94b55-04ee-8185-73a9-681b968fc7b8@aklaver.com> In-Reply-To: <66a94b55-04ee-8185-73a9-681b968fc7b8@aklaver.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=aquamonix.com.au; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: SY4P282MB1052:EE_|ME3P282MB3017:EE_ x-ms-office365-filtering-correlation-id: 040a3ac5-0bc8-4d12-a966-08db3707528b x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: 4J0JQNDdLY2YD8OFHfR0QA22ThuxAvwXeoP1p2qi/jd+EDfyD2Q+Tb7lx6p55A5orIyZ31H5gIGZM0MhCzDAAgjBYqlXuWhkuIe+KUlKHbfle+JjSiu638kAi7XT3lMZiemluKPwzyVTGYEt1oxemt/G41UiLrg5pNNPXlgJAviPM0KB6b0uWa+d/zV1vUVbM+TJ0UtdKPd3jPzNJHVcUPCP91ZMOYfndXgGxHobQ1obrYy23d5Hb5VdrHqBKcYSFJDxKSC0BWPMXF+9PKVfWipbPrKTb72mfNnOs/VbA4vxVib78LUeS4JAy709zG9sfU0qyx9znUEFKwBtMuG3VpIaDIg08iFWX65EYqw5KNhLUBEVyFQkxnTA+XETUHhkfU2YPe1tofI9OH/CLWv4b0JTmnOR/9wX93TmOsSvXsDzCL4S99TXj8wRM3iCD+jLieAh/WBQ5AWsnaRg3Vx/xuVBpFZtgiMVy+Xbs1bI/jNoto8DRBUqPNkCeKqdfcAuOwrfywLxJLFcx8w8xzrLsBhBr8yTcgmGfnsDCd2KgFw49Qff2QCG2WjEJiPWlznNaoCr37rzWrdlenSvOe8W/3PA/2zmZPRWr5WYOUxzulg= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230028)(366004)(376002)(136003)(346002)(39840400004)(396003)(451199021)(71200400001)(7696005)(478600001)(186003)(110136005)(316002)(9686003)(26005)(6506007)(966005)(44832011)(53546011)(2906002)(41300700001)(38070700005)(64756008)(66556008)(66946007)(66446008)(66476007)(5660300002)(76116006)(52536014)(8936002)(8676002)(38100700002)(122000001)(66574015)(86362001)(83380400001)(33656002)(55016003)(166002)(19627405001);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?D15LKKy7g+PImB85HVoeYoKoxkcCVl5ilDnLbVx9rtgGR/fZmXcUaKO3+CCa?= =?us-ascii?Q?51q7ZQiToOwzNmisfCCDsa73DsDyw8YYlwwrE8/jMzbw6OlBJslhEHLIV9ea?= =?us-ascii?Q?ReMne9FWrwSt3F5kqbgetTn0y1tARMlwskojgS/Y9kNx/994V3d8qaZr+8Rz?= =?us-ascii?Q?EcLX0eioPjkxmiXHsqXm/7EuVhsMBEpOECZu1eHF565OgU+vQ1KCTMcRwSXl?= =?us-ascii?Q?VtOZicRgirB+jvMi0yRF/Cwfo8FOl6HhsjLo/U3kd7VIW/+gb71Q6Tu3VoYD?= =?us-ascii?Q?2eimPH/OahexOp85V5Ov96XJhx860LEUWA+miKaLbHf5tX0ZCTKV345xjVFt?= =?us-ascii?Q?hMEHDDNPye2Q3tWPHe3x6m78nCtySkF9YA5pDeEc7RNcgO6EI7fPxQmhHHPz?= =?us-ascii?Q?NqQgWEnWUfeqmCmuGSE12iN8OZwFtURX3m3/Bir7wMYMqTF7jm6ekonAEgZC?= =?us-ascii?Q?bkQ283IWj7BkJoBAywMzXgp0/zt0E/xusFEypHRrX6y9+/2A2bAuTvC/FEZ5?= =?us-ascii?Q?ZrP6PU+xYP6a5refVkKicFTxB+JUVzKUpJvmp4iOZrB14R40IBLwFNrGrEjp?= =?us-ascii?Q?UE+EAHhYCF71/lA+xHzYYQumGx0Ljunj75rn237rRGz3RzbvYAulb38IlpaL?= =?us-ascii?Q?aDrm/x+l91eFhdzStWjKpk7eC+F0kL4HomcflAWHfmkODoU9DjGxc7E+96XA?= =?us-ascii?Q?i0+vW8LI1ZIkUiXshvZim01NTZqi+WMclq731o0YXPLYEvY4J6TaIxn33m8C?= =?us-ascii?Q?KJj1GBISc83regNHnUozEllMxqjcIFoFsxyP85L5FRqDd76I1eOplqaUdMRv?= =?us-ascii?Q?/e4WxA0zp63/TbVO08xB47X3+/PhfYFupen2rGAEKwic1H6rAmWU5DIfM5ec?= =?us-ascii?Q?h0NWk67brjAmTwO33mH5H1ZhwedEdiD8iEDfg53L4vfeyfZQFH+oJ5wEqG5h?= =?us-ascii?Q?Pha942Cjn/IZOHvDwdMcgT4ChhR3Y++Qv3VD10WM0xftrmV0QZ/CL26P/lx3?= =?us-ascii?Q?uyUQ4RUqKSVyjFf4ZigFMzi/MiQscfm2isTegJUIEKm90zhR5GaR5PjOYVrL?= =?us-ascii?Q?rLGzcvi7ew2oHY1+fK7yN89Fw556OyENqN8g7K/IRwUKOqqojdGJo7Q9Vin9?= =?us-ascii?Q?yRDZorxUd0tkagigimqvTtDD0y1EaXBVm3PplbeiPg+nRi1vKV+jtK6fldBE?= =?us-ascii?Q?w6fhKWMUVQWhXb3vRBulqWz842J9f1dEM0CW2ZZRWZ46WjUlWjowD1GmzTfL?= =?us-ascii?Q?Xzn1oIuH+1+PDwntXhEjNDPrbnUmkCzS3TgE0zLD/B/QZWRTBfTHtEs8GqME?= =?us-ascii?Q?fUvmdwScmpU46k5V1r3dfObcrrFjbcTDXy3nTK50Tycn9DP7sY2hkYJuvQol?= =?us-ascii?Q?YdJqsChoMvEm4sDuNOVcctDtAhYgtsL3ZXT/KKgVEPJTaGj6WYgXZD2sAw0x?= =?us-ascii?Q?+n1SPUCJeWhE+57P6SxGUJ41FuopzFs/k1lSn869pFKFi6kO+qln5ckUmdRg?= =?us-ascii?Q?An8R5KdI/Q53/nRDRYFBELFinCBc+e6oe5UYTIww1x/HDmkw7j78o/6ppkky?= =?us-ascii?Q?hZ0xfqhTig+OELiAH21kgkgaNcmrK2MvFUYkrw+OmudclAQm7/IHP7NNxim9?= =?us-ascii?Q?cg=3D=3D?= Content-Type: multipart/alternative; boundary="_000_SY4P282MB1052E561074B2D7CE7F6D286A6969SY4P282MB1052AUSP_" MIME-Version: 1.0 X-OriginatorOrg: aquamonix.com.au X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 040a3ac5-0bc8-4d12-a966-08db3707528b X-MS-Exchange-CrossTenant-originalarrivaltime: 07 Apr 2023 01:27:59.1809 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 0c1f77a4-88ee-41e8-865f-5d8ce56b5c6f X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: KWiyz9BgG8jVmlU9qVI0gmY8bZ9UBnuMGPH/hF8Dx3hBQstxpvpJP/aajmr7Eg7fBIuA/DX7SBLoCTXp/K2qqig6wQIo/MuTXmP0QhPQLlc= X-MS-Exchange-Transport-CrossTenantHeadersStamped: ME3P282MB3017 X-MailGuard-UID: 642f71a41da7d9a6 X-MailGuard-ID: 642f71a5b804aa List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SY4P282MB1052E561074B2D7CE7F6D286A6969SY4P282MB1052AUSP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Adrian, Thank you. I think this is a better approach than trigger-based solution, a= t least for my taste. That being said, it does require some logic to push to the client side (fig= uring out which required column value is missing and set it value to the ex= isting one via reference of the table name). Still wish there would be UPSERT statement that can handle this and make de= v experience better. Cheers, Louis Tian ________________________________ From: Adrian Klaver Sent: Friday, April 7, 2023 7:00 AM To: Louis Tian ; pgsql-general@lists.postgresq= l.org Subject: [EXTERNAL]: Re: UPSERT in Postgres CAUTION: This email originated from outside of Envirada. Do not click links= or open attachments unless you recognize the sender and know the content i= s safe. On 4/5/23 23:21, Louis Tian wrote: > This is a question/feature request. > > Given the definition of upsert, I'd expect an upsert command to do the fo= llowing. > - `upsert into person (id, name) values (0, 'foo')` to insert a new row > - `upsert into person (id, is_active) values (0, true)` updates the is_ac= tive column for the row inserted above > > Naturally, since there isn't a real upsert command in PostgreSQL this won= 't work today. > But can we achieve the same effect with "INSERT ... ON CONFLICT DO UPDATE= " like a lot of references on the internet seems to suggest. > > insert into person (id, name) values (0, 'foo') on conflict ("id") do upd= ate set id=3Dexcluded.id, name=3Dexcluded.name > insert into person (id, is_active) values (0, true) on conflict ("id") do= update set id=3Dexcluded.id, is_active=3Dexcluded.is_active insert into person (id, name, is_active) values (0, '', true) on conflict ("id") do update set id=3Dexcluded.id, name=3Dperson.name, is_active=3Dexcluded.is_active ; INSERT 0 1 select * from person; id | name | is_active ----+------+----------- 0 | foo | t > > Unfortunately. the second statement will fail due to violation of the not= null constraint on the "name" column. > PostgreSQL will always try to insert the row into the table first. and on= ly fallback to update when the uniqueness constraint is violated. > Is this behavior wrong? maybe not, I think it is doing what it reads quit= e literally. > That being said, I have never had a need for the ON CONFLICT DO UPDATE st= atement other than where I need upsert. > But using it as "upsert" is only valid when the table is absent of any NO= T NULL constraint on it's non primary key columns. > So, if my experience/use case is typical (meaning the main purpose / use = case for ON CONFLICT DO UPDATE is to support upsert) then it can be argue t= he current behavior is incorrect? > > This has been a source confusion to say at least. > https://stackoverflow.com/questions/48816629/on-conflict-do-nothing-in-po= stgres-with-a-not-null-constraint > https://www.postgresql.org/message-id/flat/1996438.1604952901%40sss.pgh.p= a.us#ace8adc1354265aca5672028faea0c0f > > The MERGE command introduced in PG15 in theory can be used to do UPSERT p= roperly that is void of the aforementioned limitation. > The downside is it is rather verbose. > > *Question* > This there a way to do an upsert proper prior to PG15? > > *Feature Request* > Given that UPSERT is an *idempotent* operator it is extremely useful. > Would love to see an UPSERT command in PostgreSQL so one can 'upsert' pro= perly and easily. > > > Regards, > Louis Tian > > > > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com -- Message protected by MailGuard: e-mail anti-virus, anti-spam and content f= iltering.https://www.mailguard.com.au/mg Click here to report this message as spam: https://console.mailguard.com.au/ras/259w94L3yw/5Vb4QHWbRwfP3KeOMAvzU8/-2.1 --_000_SY4P282MB1052E561074B2D7CE7F6D286A6969SY4P282MB1052AUSP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi Adrian, 

Thank you. I think this is a better approach= than trigger-based solution, at least for my taste.
That being said, it does require some logic to push to the client side (fig= uring out which required column value is missing and set it value to the ex= isting one via reference of the table name). 
Still wish there would be UPSERT statement t= hat can handle this and make dev experience better.

Cheers,
Louis Tian

From: Adrian Klaver <adr= ian.klaver@aklaver.com>
Sent: Friday, April 7, 2023 7:00 AM
To: Louis Tian <louis.tian@aquamonix.com.au>; pgsql-general@li= sts.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: [EXTERNAL]: Re: UPSERT in Postgres
 
CAUTION: This email originated from outside of Env= irada. Do not click links or open attachments unless you recognize the send= er and know the content is safe.


On 4/5/23 23:21, Louis Tian wrote:
> This is a question/feature request.
>

> Given the definition of upsert, I'd expect an upsert command to do the= following.
> - `upsert into person (id, name) values (0, 'foo')` to insert a new ro= w
> - `upsert into person (id, is_active) values (0, true)` updates the is= _active column for the row inserted above
>
> Naturally, since there isn't a real upsert command in PostgreSQL this = won't work today.
> But can we achieve the same effect with "INSERT ... ON CONFLICT D= O UPDATE" like a lot of references on the internet seems to suggest. >
> insert into person (id, name) values (0, 'foo') on conflict ("id&= quot;) do update set id=3Dexcluded.id, name=3Dexcluded.name
> insert into person (id, is_active) values (0, true) on conflict ("= ;id") do update set id=3Dexcluded.id, is_active=3Dexcluded.is_active
insert into person (id, name, is_active) values (0, '', true) on
conflict ("id") do update set id=3Dexcluded.id, name=3Dperson.nam= e,
is_active=3Dexcluded.is_active ;
INSERT 0 1

select * from person;
  id | name | is_active
----+------+-----------
   0 | foo  | t

>
> Unfortunately. the second statement will fail due to violation of the = not null constraint on the "name" column.
> PostgreSQL will always try to insert the row into the table first. and= only fallback to update when the uniqueness constraint is violated.
> Is this behavior wrong? maybe not, I think it is doing what it reads q= uite literally.
> That being said, I have never had a need for the ON CONFLICT DO UPDATE= statement other than where I need upsert.
> But using it as "upsert" is only valid when the table is abs= ent of any NOT NULL constraint on it's non primary key columns.
> So, if my experience/use case is typical (meaning the main purpose / u= se case for ON CONFLICT DO UPDATE is to support upsert) then it can be argu= e the current behavior is incorrect?
>
> This has been a source confusion to say at least.
> https://stackoverflow.com/questions/48816629/on-conflict-do-nothing-in-post= gres-with-a-not-null-constraint
> https://www.postgresql.org/message-id/flat/1996438.1604952901%40sss.pgh.pa.= us#ace8adc1354265aca5672028faea0c0f
>
> The MERGE command introduced in PG15 in theory can be used to do UPSER= T properly that is void of the aforementioned limitation.
> The downside is it is rather verbose.
>
> *Question*
> This there a way to do an upsert proper prior to PG15?
>
> *Feature Request*
> Given that UPSERT is an *idempotent* operator it is extremely useful.<= br> > Would love to see an UPSERT command in PostgreSQL so one can 'upsert' = properly and easily.
>
>
> Regards,
> Louis Tian
>
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Message  protected by MailGuard: e-mail anti-virus, anti-spam and cont= ent filtering.https://www.mailguard.com.au/mg
Click here to report this message as spam:
https://console.mailguard.com.au/ras/259w94L3yw/5Vb4QHWbRwfP3K= eOMAvzU8/-2.1

--_000_SY4P282MB1052E561074B2D7CE7F6D286A6969SY4P282MB1052AUSP_--