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 1q43Bz-0005qZ-AV for pgsql-general@arkaria.postgresql.org; Tue, 30 May 2023 17:31:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q43By-0003E2-0y for pgsql-general@arkaria.postgresql.org; Tue, 30 May 2023 17:31:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q43Bx-0003Dt-MO for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 17:31:29 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q43Bu-0036lE-Ci for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 17:31:29 +0000 Received: from pro.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 34UHVL3q2822184; Tue, 30 May 2023 13:31:21 -0400 From: Tom Lane To: "David G. Johnston" cc: Marc Millas , Adrian Klaver , "pgsql-general@lists.postgresql.org" Subject: Re: syntax pb In-reply-to: References: <02bd7734-9e59-8bbf-f39b-e0bc484c3912@aklaver.com> Comments: In-reply-to "David G. Johnston" message dated "Tue, 30 May 2023 10:11:47 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <1854.1685467881.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Tue, 30 May 2023 13:31:21 -0400 Message-ID: <1855.1685467881@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "David G. Johnston" writes: > On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote >> Too my understanding it looks like the parser did not parse the select >> distinct as we think he does. > The DISTINCT clause doesn't really come into play here at all, so if you > think it does you indeed have a misunderstanding. No, he's correct: postgres=# create table z (f1 int); CREATE TABLE postgres=# insert into z values(null); INSERT 0 1 postgres=# insert into z select null; INSERT 0 1 postgres=# insert into z select distinct null; ERROR: column "f1" is of type integer but expression is of type text LINE 1: insert into z select distinct null; ^ HINT: You will need to rewrite or cast the expression. The first two INSERTs are accepted because there's nothing "between" the untyped NULL and the INSERT, so we can resolve the NULL as being of type int. But use of DISTINCT requires resolving the type of the value (else how do you know what's distinct from what?) and by default we'll resolve to text, and then that doesn't match what the INSERT needs. regards, tom lane