From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Marc Millas <marc.millas@mokadb.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: syntax pb
Date: Tue, 30 May 2023 13:31:21 -0400
Message-ID: <1855.1685467881@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwZf_jnHZaoA5w7y92MsBVfS8-jpLhfMCGB4_A7+uO7gcQ@mail.gmail.com>
References: <CADX_1aZ5gMfoR+D7LpsQ2jq7N1AFcpVCh6TP1N0deZdqNaPPrg@mail.gmail.com>
<02bd7734-9e59-8bbf-f39b-e0bc484c3912@aklaver.com>
<e891c59a-cf22-4b8b-6de0-da3614483808@aklaver.com>
<CADX_1aZaUNLBjfkVAA02rkx8vJWUrVORXS38Ubj4+8T6G5r5Bw@mail.gmail.com>
<CAKFQuwZf_jnHZaoA5w7y92MsBVfS8-jpLhfMCGB4_A7+uO7gcQ@mail.gmail.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, May 30, 2023 at 8:53 AM Marc Millas <marc.millas@mokadb.com> 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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, marc.millas@mokadb.com, adrian.klaver@aklaver.com
Subject: Re: syntax pb
In-Reply-To: <1855.1685467881@sss.pgh.pa.us>
References: <CADX_1aZ5gMfoR+D7LpsQ2jq7N1AFcpVCh6TP1N0deZdqNaPPrg@mail.gmail.com> <02bd7734-9e59-8bbf-f39b-e0bc484c3912@aklaver.com> <e891c59a-cf22-4b8b-6de0-da3614483808@aklaver.com> <CADX_1aZaUNLBjfkVAA02rkx8vJWUrVORXS38Ubj4+8T6G5r5Bw@mail.gmail.com> <CAKFQuwZf_jnHZaoA5w7y92MsBVfS8-jpLhfMCGB4_A7+uO7gcQ@mail.gmail.com> <1855.1685467881@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox