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 1rbPQS-00FHil-Vh for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 18:28:36 +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 1rbPQR-007kCG-Mk for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 18:28:35 +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 1rbPNF-007eEv-55 for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 18:25:17 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbPNC-007FOp-9S for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 18:25:15 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1708194312; x=1708799112; i=karsten.hilbert@gmx.net; bh=OsS58HzL947MrTBFjo50V0pcOhAc0xsWLtP8qx6ls5Q=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:References: In-Reply-To; b=nJBTHw71Nv7w4NipI7UHUs5yokr9vfmOsPRlZ/jhwRF3OI5tGRa/q1UQecmuV+/W AdG8HBvyMxEFXR9gELN4bQ+0n/JKX8RPmRSLRS6/b3WNtKoWJASpOmwhKDy7zv32W hDl25Cf7Gevis0I5thzmc9oVNEIUAttIutijFG+2lPk3+97cYcGxbQ7YogS8KmZwr tN5PGd+ZZTNJFznKSFgtejmD5RcUkLV9oOkfFlxG2zpiTRroo8lx+/VDTuKQU7Z1E ihBZrohtiUz7gbV+TYj1H0IfaIuyl6K7Ke5k2UYHExGVeKDx9MujUolfXrwDxAqiM BiZLvJkVOB6aqu0Dtw== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([84.144.207.30]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1N6KUT-1qrDos2Xtp-016dZI; Sat, 17 Feb 2024 19:25:11 +0100 Received: from ncq by hermes with local (Exim 4.96) (envelope-from ) id 1rbPN9-0001Z9-0B; Sat, 17 Feb 2024 19:25:11 +0100 Date: Sat, 17 Feb 2024 19:25:11 +0100 From: Karsten Hilbert To: Greg Sabino Mullane Cc: pgsql-general@lists.postgresql.org Subject: Re: "reverse" (?) UPSERT -- how to ? Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: 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:reK7qLhj+8V05DsGHD1Pg9DLXSMViun3AzottQcE4od1berNDdJ kIh/leDI0q6oYJ4lNF4xa9LUki8ugjHn9E/HbbKyVYrGJK1XT/xgMjvx1xwXpdORr7lven7 PTOPkClUXJrRRD9h+yhXjJMRs3r+wIq6ypzdWVRM5G0Se1Xe9C6nxq0dsBNCx1ho431Mx9i YJivm3pILoHMi4xVCISFg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:l6tvzStDuF0=;i+3u25CgM6rbPe4PBb7j4YeDV5T WBlERMiOpdbMsAoywbRfP6GvbQJdP2nJU/CECDSq1DmFOu5RdZ3nH9DJxgtzyKLDlfVTdEJvG MF1rRApJ5tJ+xncesBb2hVIsmRH0gfk+0kGkTOLKknTqky9OdGIxq+l3xcm24Yk8VD7wDJL2T CoEhDqfzoUzbpLqExvfCUXg6RNVj07SrnmKAZQ86X0HPQZBHj9Mzm8UGu9HPezGU+6hfABTCr ztho3MbrPjENaRE89KDOXQLpJwp/oO4TpwWJvO3AO2t4k94voMN10Wea+uSgmA/IPeTCy8/24 uDHqCtuxdLrMrLh/TmOz7Zmbjai9rLnV4AaebNmjDyZUUUYVQjRP5WgOs3/XZjGBlwGtY0pf+ c+G3a/GboAx4D1LyDUtnqZloYRn51+nB01b3slY4ysmJMBaxXVjHB7cyj0LjkSe66F/8rmg35 oYs51vn3Da+Mxu3VINSpLoia8sfa6PHe4YUXM2E5h8Tj5u0m92lrAifiizojoKh3Id78UN6e7 2TjWPXkbrIdaaPxD+iBO9nf7DUR25NaG7hNRx/z+01r9xPHekPPE6pXgWVZ4wyPMkTRDsg1Hs rFm8eRGsL0NCSpqfpY39r0US0Ivh0OYh09t0Z4fmIeSUmDZyj5MWaENWjZCeRtxjLFdMcBTAY YfCUUhr0Jq7AVn5I25Dm/FMdzNgfDA6Ci97mZ1JvAdPpnQqvunRTN4huqgkEebS+2gFq+EXa3 nUVSm5gqu2rvWKT7fr881SW6ZGpSclOtadHdQIX5gPbsJH2c0WO6FFFroItYP7fR1y0FUkHYv nW9OfGosCaE3BLn3LV77VFgxxq5WOWwst57PVkxSEPLr4= 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 11:54:05AM -0500 schrieb Greg Sabino Mullane: > > There will be a view giving rows for > > each detail row enriched with master table data > > UNION ALL > > rows for each master row that does not have any detail row wit= h > > detail table columns NULLed > > > > A better way to do that is to just use a LEFT JOIN. +1 except that I've got use for both parts of the UNION on their own (they are both views themselves involving a bunch of joins with yet other tables, 4 or 5 each or so ;-) Thanks, Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B