Feedback-ID: i76984098:Fastmail
Message-ID: <37e09717-f121-4192-b152-18df17713414@aklaver.com>
Date: Fri, 9 Aug 2024 14:26:50 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Insert works but fails for merge
To: yudhi s <learnerdatabase99@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/37e09717-f121-4192-b152-18df17713414%40aklaver.com>
Precedence: bulk

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@aklaver.com