Thanks for the explanation. Crystal clear, thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com
On Tue, May 30, 2023 at 7:31 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johns...@gmail.com> writes: > > On Tue, May 30, 2023 at 8:53 AM Marc Millas <marc.mil...@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 >
