Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCSA9-0004eg-Ks for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Jul 2022 20:43:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oCSA8-000636-C6 for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Jul 2022 20:43:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCSA8-00062E-0K for pgsql-hackers@lists.postgresql.org; Fri, 15 Jul 2022 20:43:48 +0000 Received: from mail-qk1-x734.google.com ([2607:f8b0:4864:20::734]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oCSA5-0007hE-4e for pgsql-hackers@postgresql.org; Fri, 15 Jul 2022 20:43:46 +0000 Received: by mail-qk1-x734.google.com with SMTP id l3so2326473qkl.3 for ; Fri, 15 Jul 2022 13:43:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=6msF6pkEM6ox1reY+/UI5c6KcsMY3AZmXmojMy0IYQA=; b=d3yo6D8IaAd6r3a7zwMWJiXPVN+JD5wXMYvW5R3gX4T7aI/lpqt07MUT3xaRK+YcO5 IBX4akxjbsBGigl1qajpI6lgDn5FPH8TnWSNczO5+SR+5GgBwTZhaiYw5tgiXGdIRmtE k3cT61fjBne+Esfv8MHKjv81NW4NoNOB14UVUOpu7bnxDN2sbjszpX2+66kgJmBIkAbL w6W7r/YoGQeRiV5crXkNLligZnrrv9iCNCqjy1d2Yj3lSSZ3VodLFFLp8JWA9LDQAZoW 9VJK87cjn7RLpdQIzJaJBbJcgeM/LWskSpVgGIGGfyWYV4LyxfXz14IbNLfLzYRUhWpC e2UA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=6msF6pkEM6ox1reY+/UI5c6KcsMY3AZmXmojMy0IYQA=; b=fBf6yPnLPb6pqkkJ7sjqyH8f5q0u8+sayGXLVUrZdyXEOlDabdyCfU3t0lWsaz1V00 kNfavQLO9gX6Tm2cQ0CtUj1sM+QzptHtzwQRkIXUZ8w43FhHTfqObuuTiJc8Xe5mzgeP qIqSw1OVomloBTqraJfkVDFdOqB54tPxZqrto5C6U8g+m3QZt7Uih59RS7yYSG/F4nfa sHrptncGLuYmjQElNm5l+qAej/FTlCGvga6Me6C+4tBR/DaYXH21MGk4RNv08m5Z3ZH6 Rv0IpZlfY9f/rpvx6UBDTxwjZ39MpC6B70bcUcNwTKvBF0mqBlrYe+OPfxqSjQ/Lihiv 9m/g== X-Gm-Message-State: AJIora+2Z4vbIfzhjcC+z289YT+96ziBpdI7xHbBQRN2gXeT+2Mbx1iG eckPSE/aO51RrXpnm2tkzekqug== X-Google-Smtp-Source: AGRyM1sN6/SirIOQ8y3mGtIL7Q7tayU2ssraCKPnvjIXEl8yV2LPkTgADF4ovmvwixzARpHuyX2jKg== X-Received: by 2002:a05:620a:28c5:b0:6b2:52c9:6bea with SMTP id l5-20020a05620a28c500b006b252c96beamr10983316qkp.568.1657917824016; Fri, 15 Jul 2022 13:43:44 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id n123-20020a37bd81000000b006a3325fd985sm4502985qkf.13.2022.07.15.13.43.43 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 15 Jul 2022 13:43:43 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 1F765800D6A; Fri, 15 Jul 2022 15:43:42 -0500 (CDT) Date: Fri, 15 Jul 2022 15:43:41 -0500 From: Justin Pryzby To: "David G. Johnston" Cc: Alvaro Herrera , Matthias van de Meent , pgsql-hackers@postgresql.org Subject: Re: MERGE and parsing with prepared statements Message-ID: <20220715204341.GO18011@telsasoft.com> References: <20220715175854.GM18011@telsasoft.com> <20220715184019.rour3mk33ngc76y6@alvherre.pgsql> <20220715194013.GN18011@telsasoft.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, Jul 15, 2022 at 12:59:34PM -0700, David G. Johnston wrote: > On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby wrote: > > > > | If the expression for any column is not of the correct data type, automatic type conversion will be attempted. > > That appears to be copied from the INSERT page. > > What does that mean, if not that data types will be resolved as needed ? > > Yep, and the system needs to resolve the type at a point where there is no > contextual information and so it chooses text. I don't know if that's a viable interpretation. The parser "resolved the type" by assuming a default, which caused an error before finishing parsing. In the case of INSERT, "conversion will be attempted" means it looks for a cast from the source type to the target type, and its "automatic type conversion" will fail if (for example) you try to insert a timestamp into an int. In the case of MERGE, that same sentence evidently means that it assumes a default source type (rather than looking for a cast) and then fails if it doesn't exactly match the target type. Should that sentence be removed from MERGE ? -- Justin