Re: syntax pb

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Tue, May 30, 2023 at 8:53 AM Marc Millas <marc(dot)millas(at)mokadb(dot)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
>