public inbox for [email protected]
help / color / mirror / Atom feedFrom: yudhi s <[email protected]>
To: Adrian Klaver <[email protected]>
To: David G. Johnston <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Insert works but fails for merge
Date: Sat, 10 Aug 2024 17:37:31 +0530
Message-ID: <CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
<[email protected]>
On Sat, Aug 10, 2024 at 2:56 AM Adrian Klaver <[email protected]>
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 be
> > executed through merge , it fails stating "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 (
> > 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 = EXCLUDED.mid,
> > txn_timestamp = EXCLUDED.txn_timestamp,
> > cre_ts = 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 = source.id
WHEN MATCHED THEN
UPDATE SET mid = source.mid
WHEN NOT MATCHED THEN
INSERT (id, mid, txn_timestamp, cre_ts)
VALUES (source.id,source.mid, source.txn_timestamp, source.cre_ts);
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Insert works but fails for merge
In-Reply-To: <CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox