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)
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
