Search Postgresql Archives

Re: syntax pb

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks for the explanation. Crystal clear, thanks

Marc MILLAS
Senior Architect
+33607850334



On Tue, May 30, 2023 at 7:31 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes:
> On Tue, May 30, 2023 at 8:53 AM Marc Millas <marc.millas@xxxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux