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 1scscf-00B8Tf-1b for pgsql-general@arkaria.postgresql.org; Sat, 10 Aug 2024 20:23:33 +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 1scscd-00AM1H-3r for pgsql-general@arkaria.postgresql.org; Sat, 10 Aug 2024 20:23:31 +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 1scscc-00AM19-NG for pgsql-general@lists.postgresql.org; Sat, 10 Aug 2024 20:23:30 +0000 Received: from mail-il1-x12d.google.com ([2607:f8b0:4864:20::12d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1scscX-004B6d-Dm for pgsql-general@lists.postgresql.org; Sat, 10 Aug 2024 20:23:27 +0000 Received: by mail-il1-x12d.google.com with SMTP id e9e14a558f8ab-39b3ffc570bso13999545ab.3 for ; Sat, 10 Aug 2024 13:23:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723321404; x=1723926204; 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=NSmfa97cJ+S9QTuKfa+dmAcnVpdXkfGK8ZNdAt8O9vg=; b=aPoh03Q1zYJNL11tQWVhgWNQ1IZxqy2OfgaGx/4rebYbMNb28Lj6FtZS94MUFNklP1 fcINPIYJ76ozTn12oOvZLVtaQnmwVMhc/anroW2ah1smsr9ZZ23VtHlu6tH98PlwBkUX qTSc0312neIPH154sIvOlBN9ecZ5286ivluYXX454XtNbFZSzkMwiEtha5xqosQPbJza ZFQZ2qslUorM7+fgv+FNI0oHwsLrl1FPr56hNWsJrhb7+K7l40gwI9vL/iyw58SBsLP6 3iL5BSPf7MPjsXmHgl7gZeBk2gdPzS1bdkzgT5kNAek0Qvmn0Z2jhEGpGwwc1ZqHwrl6 RTDQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723321404; x=1723926204; 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=NSmfa97cJ+S9QTuKfa+dmAcnVpdXkfGK8ZNdAt8O9vg=; b=Ws6OHK1l0GIVNdXh9lLpuEcbSpy2uIqfGzAgMnRnYLp+Ha9Et0f1ALC0acBrLckXbP AXEhJsZf1jhI6S1X0sDGVID5YWZhEgjPDx+fzLx0iYHCKPGE/o9JWEGz5TvcVL6WbPHA 9dQThnmZt04OzhoNjOFMP1iKMvCCI7DIjS+ysdKCb448M5lvZmsGdBPF4RkOWLxdmmxU X/l4IEZJQWEsGsonm6T/6Cbgx1suQ8VxJx5gbreXkHITy+3IzhWj65ryTHmmdBSekr+w WmxECblyzQW7r2jl6VRBN9dF1WnKa1X9CBtUbp5da6d9vDtnXoTB3MzHWBsOVYMwcDrp XeJw== X-Forwarded-Encrypted: i=1; AJvYcCUwXi1uwFPmttepyYvUP/+jjiS8MLDOGOsNjObR8lGD9TJ6jmIhsj3FOLDMugdcnBNAEhI06iQhEOT1fjCx@lists.postgresql.org X-Gm-Message-State: AOJu0YyJ1Cqo/kE0dqZH+0EMX4M5fVA0zkxIpyQZH7lUApFI1OwqwzSb IWojSRc/1ITxPQsVTcViiLF8KHievp8Kwl2lMyrng2BCZwBpY4vKch8fdBORcPPoSRkMid3Q1gf IKOCmRbh7zhaN90ezmYcE2xaFpjs= X-Google-Smtp-Source: AGHT+IGlGnMi+UkihKR4HJwANaUJHKiCKGZBWk1Us7wut/6QQQH4bWFbGS2x+b07lzjlsMkN7cEQV2bC4r7jWT3zxSk= X-Received: by 2002:a05:6e02:1fe1:b0:39a:14f7:1c1c with SMTP id e9e14a558f8ab-39b813286b9mr64189055ab.25.1723321403564; Sat, 10 Aug 2024 13:23:23 -0700 (PDT) MIME-Version: 1.0 References: <37e09717-f121-4192-b152-18df17713414@aklaver.com> <9dc702ca-8bb3-442e-bd2b-12abe81a84d0@aklaver.com> In-Reply-To: <9dc702ca-8bb3-442e-bd2b-12abe81a84d0@aklaver.com> From: yudhi s Date: Sun, 11 Aug 2024 01:53:11 +0530 Message-ID: Subject: Re: Insert works but fails for merge To: Adrian Klaver Cc: "David G. Johnston" , pgsql-general Content-Type: multipart/alternative; boundary="00000000000017ee8f061f5a0840" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000017ee8f061f5a0840 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Aug 10, 2024 at 8:22=E2=80=AFPM Adrian Klaver wrote: > > > Why not use INSERT ... ON CONFLICT instead of MERGE? > > > > > MERGE INTO tab1 AS target > > USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123, > > '2024-08-09T11:33:49.402585600Z','2024-08-09T11:33:49.402585600Z')) AS > > source(id, mid,txn_timestamp, cre_ts) > > ON target.id =3D source.id > > WHEN MATCHED THEN > > UPDATE SET mid =3D source.mid > > WHEN NOT MATCHED THEN > > INSERT (id, mid, txn_timestamp, cre_ts) > > VALUES (source.id ,source.mid, > > source.txn_timestamp, source.cre_ts); > > > Actually , as per the business logic , we need to merge on a column which is not unique or having any unique index on it. It's the leading column of a composite unique key though. And in such scenarios the "INSERT.... ON CONFLICT" will give an error. So we are opting for a merge statement here, which will work fine with the column being having duplicate values in it. --00000000000017ee8f061f5a0840 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sat, Aug 10, 2024 at 8:22=E2=80=AF= PM Adrian Klaver <adrian.kl= aver@aklaver.com> wrote:


Why not use INSERT ... ON CONFLICT instead of MERGE?

>
> MERGE INTO tab1 AS target
> USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123, > '2024-08-09T11:33:49.402585600Z','2024-08-09T11:33:49.4025= 85600Z')) AS
> source(id, mid,txn_timestamp, cre_ts)
> ON t= arget.id <http://target.id> =3D source.id <http://source.id>
> WHEN MATCHED THEN
> UPDATE SET mid =C2=A0=3D source.mid
> WHEN NOT MATCHED THEN
> INSERT (id, mid, txn_timestamp, cre_ts)
>=C2=A0 =C2=A0 =C2=A0 VALUES (source.id <http://source.id>,source.mid,
>=C2=A0 =C2=A0source.txn_timestamp, source.cre_ts);



Actually , as per the business log= ic , we need to merge on a column which is not unique or having any unique = index on it. It's the leading column of a composite unique key though. = And in such scenarios=C2=A0the "INSERT.... ON CONFLICT" will give= an error. So we are opting for a merge statement here, which will work fin= e with the column being having=C2=A0duplicate values in it.
--00000000000017ee8f061f5a0840--