Re: syntax pb

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

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(dot)klaver(at)aklaver(dot)com