public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: yudhi s <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Insert works but fails for merge
Date: Fri, 9 Aug 2024 14:26:50 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
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
[email protected]
view thread (3+ messages) latest in thread
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]
Subject: Re: Insert works but fails for merge
In-Reply-To: <[email protected]>
* 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