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 1sYpI0-00EKgJ-Ns for pgsql-novice@arkaria.postgresql.org; Tue, 30 Jul 2024 16:01:28 +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 1sYpHx-00BOQ4-0u for pgsql-novice@arkaria.postgresql.org; Tue, 30 Jul 2024 16:01:25 +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 1sYpHw-00BOPw-O7 for pgsql-novice@lists.postgresql.org; Tue, 30 Jul 2024 16:01:24 +0000 Received: from mail-oo1-xc35.google.com ([2607:f8b0:4864:20::c35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sYpHu-002HZ0-23 for pgsql-novice@lists.postgresql.org; Tue, 30 Jul 2024 16:01:23 +0000 Received: by mail-oo1-xc35.google.com with SMTP id 006d021491bc7-5d5bb03fe42so2626701eaf.3 for ; Tue, 30 Jul 2024 09:01:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1722355280; x=1722960080; 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=ylDoUxWTcsDXeEqXQXUz3ghDlavRn4PFOSBKe/+yRrE=; b=Ftk8H+DQkQIrWbZFN3YRmqh9Ek8utvfbe30AuVmxT7qLnDvgcgm5Jxo3sY/tqFV2m6 IKbbt8Cf0e9GgqTg6pN3SFOaJglCM4JOjaVGjtWSGjPj8ctq5kGVuLU272sf1Ydt3NjA f+4Q/RgYQko/g73gqKAuhaJFN+lCtdJYPCETHqOd/OGLwT/gGXOv+D1sRxT54NqUPug6 QMqhz+TWamESk9xEdcDd0Dt/t/vwdzq1Hocro3bLIXhOHtwP0XRx2ymN7TloImlB4fAR u6qEiRnEcxACduFrz7kKstK89nS2UnyVTgDQcBq/0/U2aT+MB7Qaa3M6aI5lKLz/vozJ I9iQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1722355280; x=1722960080; 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=ylDoUxWTcsDXeEqXQXUz3ghDlavRn4PFOSBKe/+yRrE=; b=dQkQjp0h3i/W62BGVc+QAhm4UjJey4HX8dwvlC4GEe5WC4bvH6HXb08UbbOAW4Jg4k kPVRvjjrV1ii4X6TmlpOyIQ/fXnRkOBMKtMfFdp99LOWiIaPsp7jp1uJ66FM6b1IZxHW zyiYaeVlVFC6Q7F8UDu66y1TsqQPiANjzQf7uHZD0DunLf2ljYBYXHvB2x/SEefhso4d w2N5FkwdcoEyA7DlEAY8Or6RP3Tp+UqnrSgsXuISzZQf/5W4b46n89Erlk00eUakOxde 1B2JhL3ljcIWb7nTdCB2Iz9eLY4PtW4nMi/Ad2HGvVnYGuCbdt5Co0XIU8haRmVQ5z0c c9zQ== X-Gm-Message-State: AOJu0Yxj7hdNMJW1JTL621qAcnGCWNPwFu1JpivwzUkxrp9EJiz1kSEx qEzyQlenPbTscYwtn1v0s8xJL5lD5zVClyZNZEzJkaxHAVLqht0gmY8/sLkvqNGZW3yoowwNGi/ 4ccZGTOkEfleazoYQKHtjddw3QPI= X-Google-Smtp-Source: AGHT+IFtNA1COusQ+g1UvObYTRKW3wXPgYEVm67GTaZJKmhE7GwJVmi64bOVPwQeSs2ESd8+0e/wmzJc5OKwtHklsgE= X-Received: by 2002:a05:6820:1b06:b0:5d6:c77:fb8a with SMTP id 006d021491bc7-5d60c7802bdmr1288916eaf.0.1722355279810; Tue, 30 Jul 2024 09:01:19 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 30 Jul 2024 09:00:42 -0700 Message-ID: Subject: Re: Support for on conflict ignore returning To: Brian McMahon Cc: pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a156d0061e79162a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a156d0061e79162a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Jul 30, 2024 at 8:54=E2=80=AFAM Brian McMahon wrote: > > Is there a recommended way to achieve a ON CONFLICT DO NOTHING RETURNING > so that conflicting columns still have information returned? > Currently it seems like the only simple way to achieve this is with a NOO= P > update, so instead doing something like ON CONFLICT UPDATE SET name =3D > EXCLUDED.name RETURNING . > This is requested frequently. As of now I'm not aware of anyone working on a patch to make it work. For the described "chained DML" use case my typical advice is to not rely upon the "default" clause to produce your surrogate keys but to produce them yourself. Also, this is one of the few areas that iterating row-by-row and doing one set of multi-table DML per transaction (or maybe savepoint...) works out reasonably well. David J. --000000000000a156d0061e79162a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Jul 30, 2024 at 8:54=E2=80=AFAM Brian McMahon <= brianmcmahon135@gmail.com&= gt; wrote:

=
Is = there a recommended way to achieve a ON CONFLICT DO NOTHING RETURNING <c= olumn> so that conflicting columns still have information returned? Curr= ently it seems like the only simple way to achieve this is with=C2=A0a NOOP= update, so instead doing something like ON CONFLICT UPDATE SET name =3D EX= CLUDED.name RETURNING <column>.

This is requested frequently.=C2=A0 As of now I'm not aware of any= one working on a patch to make it work.

For the descri= bed "chained DML" use case my typical advice is to not rely upon = the=C2=A0"default" clause to produce your surrogate keys but to p= roduce them yourself.=C2=A0 Also, this is one of the few areas that iterati= ng row-by-row and doing one set of multi-table DML per transaction (or mayb= e savepoint...) works out reasonably well.

David J.

--000000000000a156d0061e79162a--