Re: syntax pb

On 5/30/23 10:31 AM, Tom Lane wrote:
> "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.

Huh, new lesson learned:

create table t1 (t text);
create table t2 (a text, b text, c test, d numeric);
insert into t1 values('azerty');

Insert into t2 (a, b, c, d)
Select  test1.t, 'abc' as b, NULL as c, NULL as d
 From t1 test1;

INSERT 0 1

  select * from t2;
    a    |  b  |  c   |  d
--------+-----+------+------
  azerty | abc | NULL | NULL

Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
 From t1 test1

ERROR:  column "c" is of type test but expression is of type text
LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
                                              ^
HINT:  You will need to rewrite or cast the expression.


> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com