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