Content-Type: multipart/mixed; boundary="------------Yt3A810IufXzQKFxRJYe3wIK"
Message-ID: <3518c50a-ab18-482f-b916-a37263622501@deepbluecap.com>
Date: Mon, 10 Mar 2025 10:48:34 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: pgsql-bugs@lists.postgresql.org
From: Duncan Sands <duncan.sands@deepbluecap.com>
Subject: Attribute of type record has wrong type error with MERGE ... WHEN NOT
 MATCHED BY SOURCE THEN DELETE
Archived-At: 
 <https://www.postgresql.org/message-id/3518c50a-ab18-482f-b916-a37263622501%40deepbluecap.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------Yt3A810IufXzQKFxRJYe3wIK
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Postgresql version 17.4 (Ubuntu 17.4-1.pgdg24.10+2) on x86_64-pc-linux-gnu

To reproduce, execute the statements in the attached file cr.sql.  I get:

duncan=> \i cr.sql
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE TABLE
COPY 1
COPY 2
COPY 1
psql:cr.sql:42: ERROR:  attribute 2 of type record has wrong type
DETAIL:  Table has type _country_or_region, but query expects record.

I attribute it to the "WHEN NOT MATCHED BY SOURCE THEN DELETE" part of the MERGE 
as it doesn't happen if that part is left off.

Best wishes, Duncan.
--------------Yt3A810IufXzQKFxRJYe3wIK
Content-Type: application/sql; name="cr.sql"
Content-Disposition: attachment; filename="cr.sql"
Content-Transfer-Encoding: base64

Q1JFQVRFIFRBQkxFIHB1YmxpYy5jb3VudHJpZXMgKAogICAgY291bnRyeSBjaGFyYWN0ZXIo
MikgTk9UIE5VTEwsCiAgICBjb3VudHJ5X25hbWUgdGV4dCBOT1QgTlVMTCwKICAgICJudW1l
cmljIiBzbWFsbGludCBOT1QgTlVMTAopOwoKQ1JFQVRFIFRBQkxFIHB1YmxpYy5yZWdpb25z
ICgKICAgIHJlZ2lvbiBzbWFsbGludCBOT1QgTlVMTCwKICAgIHJlZ2lvbl9uYW1lIHRleHQg
Tk9UIE5VTEwKKTsKCkNSRUFURSBWSUVXIHB1YmxpYy5fY291bnRyeV9vcl9yZWdpb24gQVMK
IFNFTEVDVCBjb3VudHJpZXMuIm51bWVyaWMiIEFTIGNvdW50cnlfb3JfcmVnaW9uX2NvZGUs
CiAgICBjb3VudHJpZXMuY291bnRyeV9uYW1lIEFTIGNvdW50cnlfb3JfcmVnaW9uX25hbWUs
CiAgICB0cnVlIEFTIGlzX2NvdW50cnkKICAgRlJPTSBwdWJsaWMuY291bnRyaWVzClVOSU9O
IEFMTAogU0VMRUNUIHJlZ2lvbnMucmVnaW9uIEFTIGNvdW50cnlfb3JfcmVnaW9uX2NvZGUs
CiAgICByZWdpb25zLnJlZ2lvbl9uYW1lIEFTIGNvdW50cnlfb3JfcmVnaW9uX25hbWUsCiAg
ICBmYWxzZSBBUyBpc19jb3VudHJ5CiAgIEZST00gcHVibGljLnJlZ2lvbnM7CgpDUkVBVEUg
VEFCTEUgcHVibGljLmNvdW50cnlfb3JfcmVnaW9uICgKICAgIGNvdW50cnlfb3JfcmVnaW9u
X2NvZGUgc21hbGxpbnQgTk9UIE5VTEwsCiAgICBjb3VudHJ5X29yX3JlZ2lvbl9uYW1lIHRl
eHQgTk9UIE5VTEwsCiAgICBpc19jb3VudHJ5IGJvb2xlYW4gTk9UIE5VTEwKKTsKCkNPUFkg
cHVibGljLmNvdW50cmllcyAoY291bnRyeSwgY291bnRyeV9uYW1lLCAibnVtZXJpYyIpIEZS
T00gc3RkaW47CkFECUFuZG9ycmEJMjAKXC4KCkNPUFkgcHVibGljLmNvdW50cnlfb3JfcmVn
aW9uIChjb3VudHJ5X29yX3JlZ2lvbl9jb2RlLCBjb3VudHJ5X29yX3JlZ2lvbl9uYW1lLCBp
c19jb3VudHJ5KSBGUk9NIHN0ZGluOwoyMAlBbmRvcnJhCXQKMQlXb3JsZAlmClwuCgpDT1BZ
IHB1YmxpYy5yZWdpb25zIChyZWdpb24sIHJlZ2lvbl9uYW1lKSBGUk9NIHN0ZGluOwoxCVdv
cmxkClwuCgpNRVJHRSBJTlRPIGNvdW50cnlfb3JfcmVnaW9uIEFTIHFxX3RndCBVU0lORyBf
Y291bnRyeV9vcl9yZWdpb24gQVMgcXFfc3JjIE9OIHFxX3RndC4iY291bnRyeV9vcl9yZWdp
b25fY29kZSI9cXFfc3JjLiJjb3VudHJ5X29yX3JlZ2lvbl9jb2RlIiBXSEVOIE1BVENIRUQg
QU5EIHFxX3NyYyBJUyBESVNUSU5DVCBGUk9NIHFxX3RndCBUSEVOIFVQREFURSBTRVQgImNv
dW50cnlfb3JfcmVnaW9uX25hbWUiPXFxX3NyYy4iY291bnRyeV9vcl9yZWdpb25fbmFtZSIs
ImlzX2NvdW50cnkiPXFxX3NyYy4iaXNfY291bnRyeSIgV0hFTiBOT1QgTUFUQ0hFRCBCWSBU
QVJHRVQgVEhFTiBJTlNFUlQgKCJjb3VudHJ5X29yX3JlZ2lvbl9jb2RlIiwiY291bnRyeV9v
cl9yZWdpb25fbmFtZSIsImlzX2NvdW50cnkiKSBWQUxVRVMocXFfc3JjLiJjb3VudHJ5X29y
X3JlZ2lvbl9jb2RlIixxcV9zcmMuImNvdW50cnlfb3JfcmVnaW9uX25hbWUiLHFxX3NyYy4i
aXNfY291bnRyeSIpIFdIRU4gTk9UIE1BVENIRUQgQlkgU09VUkNFIFRIRU4gREVMRVRFOwo=


--------------Yt3A810IufXzQKFxRJYe3wIK--