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