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 1rbPJG-00FHFW-CG for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 18:21:10 +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 1rbPJE-007YEf-4k for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 18:21:08 +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 1rbPJD-007YEX-QA for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 18:21:07 +0000 Received: from mout.gmx.net ([212.227.15.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbPJ8-007hil-CB for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 18:21:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1708194057; x=1708798857; i=karsten.hilbert@gmx.net; bh=VkWQ/cNp4A64EmiZVUAbfrY0HxdTjQWPYvfeAlDAvpg=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:References: In-Reply-To; b=QldCzCeKwM7aVSq2K1dxmEUZOW3z9tZwhhUW8nSIEotcDiOshzHUN+2bFoRnPn5t czZh2vd6WjzHmLwMfSJPINM3LmJHDGih4ZZ7/WWueOXAs2vTOFQEJokAQCdeko1fg pcz5PDD6oSiLIqO9s+YE9aaUhvjGcVWCHshvdjVuoXyFTbZ0G6Ex3LS3F7zURkxjK NIzNZmvVyb4Gr1NTt9wkwwF2foPPVArEj/n5EUDNKo5z0JnlixJSDyYrSGtLV3P1x hQ9TjbPzBcG6bD2gT+uIALg1+OnS+z4K6RAZqb3gMVkD3TOAzP+2Qi4K7tkeo6fsZ rXGY2aODfRRkLS1gog== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([84.144.207.30]) by mail.gmx.net (mrgmx005 [212.227.17.190]) with ESMTPSA (Nemesis) id 1N3KTo-1qtiqx1VQY-010Q2V; Sat, 17 Feb 2024 19:20:57 +0100 Received: from ncq by hermes with local (Exim 4.96) (envelope-from ) id 1rbPJ2-0001VY-1j; Sat, 17 Feb 2024 19:20:56 +0100 Date: Sat, 17 Feb 2024 19:20:56 +0100 From: Karsten Hilbert To: Adrian Klaver , Greg Sabino Mullane Cc: pgsql-general@lists.postgresql.org Subject: Re: "reverse" (?) UPSERT -- how to ? Message-ID: References: <1afec5a0-6a5c-428e-b84f-96b0b8777a8d@aklaver.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1afec5a0-6a5c-428e-b84f-96b0b8777a8d@aklaver.com> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:KLjn1LyGQgEcz9ytkCkjNcxGKOIAou4BYs4rYz4W2pAkDI29g2j 0PcxZXjQvJwnK0q0y62Uwao06gub4FuH0MjkTxhNUcguwsMcnmXF5lhZMe+Jk8Hl4ruIT4+ tgtZ/pF85yGUK/LSobKh2I8tZl+o+M8DOu2uDF91vowiSKC2xCr36VmjoILa4NiWEtqPBuo offwIkbCbVDxouavlf76Q== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:zdbKN5eB6n8=;FM9f5TXvmxVuw+K5f6+hJvcvhQj uet/74FUnbLaU4h5Ff2fYE7Xufkg3yYXUB8sSZgaRHcny2FP7YIbYSRWmRFSzgbFBJNpj/XPI wUDcstxXfoqqW3e+E2H5+knaodXrGq4mgkRylYPaomppwFGrUoHfSYdF87yYHRWhrkcaX1Ye7 9uxXRp1kPi8tx7HIDcGzPabyR2vdTuqa+rZqVopxH6usybGmdoW9O3N6uX+0N6g8cN4BX1oir HWtKFP6oJz0BFhlUjri11XHPh8bBcsXLokjYck64jSJh/iFaUBc3W7SDong3afvNPTB3LJb0o Ybt+eX/fgs34GaZX9cBRd/CeVs/gZIyarGI+55ABQjLAiVzxQyAWDdR3Afu3MdNYAeM+ewIWo DqrlZ5qXKfuOoFsayTY+sC6tn4KdsCCFk5PJcZlAtJiPUi8/D973CXMTnzYlKCnBK7/j1CIvn heBz6lUM09MHLajQegxpKC3OTNNKc4D3Jjgb1j1eIgDRosINni0dhUsylTZogo1vPNKinlIh+ C9CccGTiB1s/GQrasFNjXaRqGloO7fBm/+joZnHIjYLCEfv75UspgXP4CaxT7ZEsniuMteIHZ ry0YozGA6a/+S9JVJPhm0jxzpWT5rjRDmesbEccqP7A6lsxxDQllYu6hoGx6ph6GWuTlDw1iD BCYHxNe2vrUIDZ6cj5zdXsRMJ3Qj8HJq14rnvZMrQ6T72OE4zpVGsH8haq8YFV2t0NzVPkXf6 ePIms+EyOeBVlriYh6rdb23x3GmkxbUspTF16lR8oT9PktmRe/pZbF+eDnDG8i47ZEWYnUFwP hRIa9Qz8lE7gEu/Q8hFqUKCXQHnacuSq+UXbi0m/g4dJM= Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am Sat, Feb 17, 2024 at 08:55:57AM -0800 schrieb Adrian Klaver: > >Dear list members, > > > > >So, is this doable within one SQL statement (short of > >creating and running the abovementioned function in > >one go ;-) ? > > Don't know what version of Postgres you are on, assuming 15+ then maybe?= : Ah, sorry, I omitted that detail :( Release target for new installs is current Debian Stable, IOW Bookworm, which has PG 15. postgresql: Installiert: 15+248 Installationskandidat: 15+248 Versionstabelle: 16+257 500 500 https://deb.debian.org/debian testing/main i386 Packages *** 15+248 990 990 https://deb.debian.org/debian bookworm/main i386 Packages 100 /var/lib/dpkg/status However, legacy deployments may run, say PG 13, root@hermes:~/bin# rmadison postgresql postgresql | 11+200+deb10u5 | oldoldstable | all postgresql | 13+225+deb11u1 | oldstable | all postgresql | 15+248 | stable | all postgresql | 16+257 | testing | all postgresql | 16+257 | unstable | all so ideally it would work on that, too. However, it does not seem too much to ask to *upgrade* GNUmed database servers to Debian *Stable* if on plans on running the next, as-yet-unreleased version ;-) > MERGE > > https://www.postgresql.org/docs/current/sql-merge.html :-) I knew I missed something. As Greg also pointed out that is what I need. Thanks ! Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B