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 1scku5-00AEMt-Fq for pgsql-general@arkaria.postgresql.org; Sat, 10 Aug 2024 12:09:01 +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 1sckt4-008O1j-IX for pgsql-general@arkaria.postgresql.org; Sat, 10 Aug 2024 12:07:58 +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 1sckt3-008O1Z-S2 for pgsql-general@lists.postgresql.org; Sat, 10 Aug 2024 12:07:58 +0000 Received: from mail-qv1-xf30.google.com ([2607:f8b0:4864:20::f30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1scksr-00429N-Tg for pgsql-general@lists.postgresql.org; Sat, 10 Aug 2024 12:07:56 +0000 Received: by mail-qv1-xf30.google.com with SMTP id 6a1803df08f44-6b7a4668f07so17261536d6.1 for ; Sat, 10 Aug 2024 05:07:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723291664; x=1723896464; 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=rgatWOw1UI3gfkS3T7mIzQ5n+H0YJ7rLFXepDvUUdeI=; b=TyEo1R9PeRpfjZDSz26K/hJ6h484wIkB18bHt1v3TsIHBSv3vjKaId5Ej7bKgmlys3 lUw8LXwmgeDIntU3IDFpLqhwzuhdOJTGwPSD9xu3sQMOSN/70kl1UBc7gglS8FyA0grO VrrUsF0rw89VdXFGu7TYkBwYIw4VoumwD4wuXoM3DHFn3g/xE0lD0a5JYf5Q6oBlYLzW 974m1xPfpGhMJf9c4C/MN8+SBedC/BqVxYbGZjYLPUcYRNvJ5y8D0LvoatIKx+8Aq1Yp HNcHJoHZMwz0mJyZxvCqzwzLQsmFQutrZhxx9ybgt2rtdx/NR55hti5EbT5PjZhA8RlJ amoA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723291664; x=1723896464; 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=rgatWOw1UI3gfkS3T7mIzQ5n+H0YJ7rLFXepDvUUdeI=; b=pg9OzAsrVazzA3ihjFC8pCTiS6GV82Cj5rRpf2JCpIikkgjGoGNlUxddusXD3QpfXY 0ikTqAKPvbq/bKC2uLpmU/3d48u5QBcy0xiZ/3+ESYRbp1EGXgqtM9HAPwEHt5GWbdxq nfWgiueAUNB9vxZUxllVnmHkOZqD5GuWDSlWvrLyw6X+xyZhUQCnulFGtL97RGbFWF28 qC3p9iciF1drsdMPLdEKjUho3L8DPHKhqNBpk9ILizHNzvzaP9a17ZEZu76f6xV3jXmp kdUdhjdG03XDG/GVn/6qvzMx8LJ90f1OC3iVirON4Rz86h62avTqDJjxyQK9a6lvzo6T qezw== X-Gm-Message-State: AOJu0YzwAtWKpzKy6PY/d5b1F2X2iUFg93L2SI2pe1WcoxbhuMVQEJ6f 8U7x97+wXrbxKEhaUIgvZlkWd2Gzz5YcCT8Wu+QGmbpDJFwZNzCbXd7TG+drK/NZ6IOabtKQjz8 Fd0TN3Mpg9ccYvgshMUuhH6t4rgo= X-Google-Smtp-Source: AGHT+IEuD+QixrrL9l+zCvDyZ86yXzfjZRGgJthFj17o/+hJpdNAkKLXSkgHNZ4Oi3D4io35Xz0l/+PLjkomioO0nzE= X-Received: by 2002:a05:6214:4a89:b0:6b5:4249:7c4 with SMTP id 6a1803df08f44-6bd78cf81ffmr44757206d6.2.1723291663951; Sat, 10 Aug 2024 05:07:43 -0700 (PDT) MIME-Version: 1.0 References: <37e09717-f121-4192-b152-18df17713414@aklaver.com> In-Reply-To: <37e09717-f121-4192-b152-18df17713414@aklaver.com> From: yudhi s Date: Sat, 10 Aug 2024 17:37:31 +0530 Message-ID: Subject: Re: Insert works but fails for merge To: Adrian Klaver , "David G. Johnston" Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000797211061f531bac" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000797211061f531bac Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Aug 10, 2024 at 2:56=E2=80=AFAM Adrian Klaver wrote: > On 8/9/24 14:13, yudhi s wrote: > > Hello, > > It's version 15.4 postgres. Where we have an insert working fine, but > > then a similar insert with the same 'timestamp' value, when trying to b= e > > executed through merge , it fails stating "You will need to rewrite or > > cast the expression.". Why so? > > > > *Example:-* > > https://dbfiddle.uk/j5S7br-q * > > * > > > > CREATE TABLE tab1 ( > > id varchar(100) , > > mid INT, > > txn_timestamp TIMESTAMPTZ NOT NULL, > > cre_ts TIMESTAMPTZ NOT NULL > > ) PARTITION BY RANGE (txn_timestamp); > > > > CREATE TABLE tab1_2024_08_09 PARTITION OF tab1 > > FOR VALUES FROM ('2024-08-09 00:00:00') TO ('2024-08-10 00:00:00')= ; > > > > -- Below insert works fine > > INSERT INTO tab1 > > (id, mid, txn_timestamp, cre_ts) > > VALUES > > ('5efd4c91-ef93-4477-840c-a723ae212d84', 123, > > '2024-08-09T11:33:49.402585600Z', '2024-08-09T11:33:49.402585600Z'); > > > > -- Below merge , which trying to insert similar row but failing > > > > WITH source_data (id, mid, txn_timestamp, cre_ts) AS ( > > VALUES ('5efd4c91-ef93-4477-840c-a723ae212d66', 123, > > '2024-08-09T11:33:49.402585600Z', '2024-08-09T11:33:49.402585600Z') > > ) > > INSERT INTO tab1 (id, mid, txn_timestamp, cre_ts) > > SELECT id, mid, txn_timestamp, cre_ts > > FROM source_data > > ON CONFLICT (id) DO UPDATE > > SET mid =3D EXCLUDED.mid, > > txn_timestamp =3D EXCLUDED.txn_timestamp, > > cre_ts =3D EXCLUDED.cre_ts; > > > > ERROR: column "txn_timestamp" is of type timestamp with time zone but > > expression is of type text LINE 24: SELECT id, mid, txn_timestamp, > > cre_ts ^ HINT: You will need to rewrite or cast the expression. > > > VALUES: > > https://www.postgresql.org/docs/current/sql-values.html > > "When VALUES is used in INSERT, the values are all automatically coerced > to the data type of the corresponding destination column. When it's used > in other contexts, it might be necessary to specify the correct data > type. If the entries are all quoted literal constants, coercing the > first is sufficient to determine the assumed type for all: > > SELECT * FROM machines > WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), > ('192.168.1.43')); > " > > The VALUES is not directly attached to the INSERT, you will need to do > explicit casts: > > VALUES ('5efd4c91-ef93-4477-840c-a723ae212d66', 123, > '2024-08-09T11:33:49.402585600Z'::timestamptz, > '2024-08-09T11:33:49.402585600Z'::timestamptz) > > > Thank You Adrian and David. Even converting the merge avoiding the WITH clause/CTE as below , is still making it fail with the same error. So it seems , only direct "insert into values" query can be auto converted/casted but not the other queries. In our case , we were using this merge query in application code(in Java) as a framework to dynamically take these values as bind values and do the merge of input data/message. But it seems we have to now cast each and every field which we get from the incoming message to make this merge work in a correct way. I am wondering if the only way now is to get the data types from information_schema.columns and then use the cast function to write the values of the merge query dynamically casted/converted for each of the fields in the application code. Please correct me if my understanding is wrong. 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); --000000000000797211061f531bac Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sat, Aug 10, 2024 at 2:56=E2=80=AF= AM Adrian Klaver <adrian.kl= aver@aklaver.com> wrote:
On 8/9/24 14:13, yudhi s wrote:
> Hello,
> It's version 15.4 postgres. Where we have an insert working fine, = but
> then a similar insert with the same=C2=A0'timestamp' value, wh= en trying to be
> executed through merge , it fails stating=C2=A0"You will need to = rewrite or
> cast the expression.". Why so?
>
> *Example:-*
> https://dbfiddle.uk/j5S7br-q <https://dbfiddle.uk/j5S7br-= q>*
> *
>
> CREATE TABLE tab1 (
>=C2=A0 =C2=A0 =C2=A0 id varchar(100) ,
>=C2=A0 =C2=A0 =C2=A0 mid INT,
>=C2=A0 =C2=A0 =C2=A0 txn_timestamp TIMESTAMPTZ NOT NULL,
>=C2=A0 =C2=A0 =C2=A0 cre_ts TIMESTAMPTZ NOT NULL
> ) PARTITION BY RANGE (txn_timestamp);
>
> CREATE TABLE tab1_2024_08_09 PARTITION OF tab1
>=C2=A0 =C2=A0 =C2=A0 FOR VALUES FROM ('2024-08-09 00:00:00') TO= ('2024-08-10 00:00:00');
>
> -- Below insert works fine
> INSERT INTO tab1
>=C2=A0 =C2=A0 =C2=A0 (id, mid, txn_timestamp, cre_ts)
> VALUES
>=C2=A0 =C2=A0 =C2=A0 ('5efd4c91-ef93-4477-840c-a723ae212d84', 1= 23,
> '2024-08-09T11:33:49.402585600Z', '2024-08-09T11:33:49.402= 585600Z');
>
> -- Below merge , which trying to insert similar row but failing
>
> WITH source_data (id, mid, txn_timestamp, cre_ts) AS (
>=C2=A0 =C2=A0 =C2=A0 VALUES ('5efd4c91-ef93-4477-840c-a723ae212d66&= #39;, 123,
> '2024-08-09T11:33:49.402585600Z', '2024-08-09T11:33:49.402= 585600Z')
> )
> INSERT INTO tab1 (id, mid, txn_timestamp, cre_ts)
> SELECT id, mid, txn_timestamp, cre_ts
> FROM source_data
> ON CONFLICT (id) DO UPDATE
> SET=C2=A0 =C2=A0 mid =3D EXCLUDED.mid,
>=C2=A0 =C2=A0 =C2=A0 txn_timestamp =3D EXCLUDED.txn_timestamp,
>=C2=A0 =C2=A0 =C2=A0 cre_ts =3D EXCLUDED.cre_ts;
>
> ERROR: column "txn_timestamp" is of type timestamp with time= zone but
> expression is of type text LINE 24: SELECT id, mid, txn_timestamp, > cre_ts ^ HINT: You will need to rewrite or cast the expression.


VALUES:

https://www.postgresql.org/docs/current/sql-v= alues.html

"When VALUES is used in INSERT, the values are all automatically coerc= ed
to the data type of the corresponding destination column. When it's use= d
in other contexts, it might be necessary to specify the correct data
type. If the entries are all quoted literal constants, coercing the
first is sufficient to determine the assumed type for all:

SELECT * FROM machines
WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.1= 0'),
('192.168.1.43'));
"

The VALUES is not directly attached to the INSERT, you will need to do
explicit casts:

VALUES ('5efd4c91-ef93-4477-840c-a723ae212d66', 123,
'2024-08-09T11:33:49.402585600Z'::timestamptz,
'2024-08-09T11:33:49.402585600Z'::timestamptz)



Thank You Adrian and David.
<= div>
Even converting the merge avoiding the WITH clause/CTE a= s below , is still making it fail with the same error. So it seems , only d= irect=C2=A0"insert into values" query can be auto converted/caste= d but not the other queries.

In our case , we were= using this merge query in application code(in Java) as a framework to dyna= mically take these values as bind values and do the merge of input data/mes= sage. But it seems we have to now cast each and every=C2=A0field which we g= et from the incoming message to make this=C2=A0merge work in a correct way.= I am wondering if the only way now is to get the data types from informati= on_schema.columns and then use the cast function to write the values of the= merge query dynamically casted/converted for each of the fields in the app= lication code. Please correct me if my understanding is wrong.=C2=A0
<= div>
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 =C2=A0=3D = source.mid
WHEN NOT MATCHED THEN
INSERT (id, mid, txn_timestamp, cre_= ts)
=C2=A0 =C2=A0 VALUES (source.id,sou= rce.mid, =C2=A0source.txn_timestamp, source.cre_ts);
=C2=A0
--000000000000797211061f531bac--