On Sat, Aug 10, 2024 at 2:56 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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);
