From: | yudhi s <learnerdatabase99(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Insert works but fails for merge |
Date: | 2024-08-09 21:13:28 |
Message-ID: | CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2024-08-09 21:24:46 | Re: Insert works but fails for merge |
Previous Message | Lok P | 2024-08-09 19:22:05 | Re: Column type modification in big tables |