[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: syntax pb
From:       Marc Millas <marc.millas () mokadb ! com>
Date:       2023-05-30 18:33:05
Message-ID: CADX_1aaYS5qz1tJoRoyU=nC7NKOBeV+4ahzEVo+2uQDwvkStCA () mail ! gmail ! com
[Download RAW message or body]

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@sss.pgh.pa.us> 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.
>
>                         regards, tom lane
>

[Attachment #3 (text/html)]

<div dir="ltr">Thanks for the explanation. Crystal clear, thanks<div><br \
clear="all"><div><div dir="ltr" class="gmail_signature" \
data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr">Marc \
MILLAS<div>Senior Architect</div><div>+33607850334</div><div><a \
href="http://www.mokadb.com" \
target="_blank">www.mokadb.com</a></div><div><br></div></div></div></div></div></div><br></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, May 30, 2023 at \
7:31 PM Tom Lane &lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">&quot;David G. \
Johnston&quot; &lt;<a href="mailto:david.g.johnston@gmail.com" \
target="_blank">david.g.johnston@gmail.com</a>&gt; writes:<br> &gt; On Tue, May 30, \
2023 at 8:53 AM Marc Millas &lt;<a href="mailto:marc.millas@mokadb.com" \
target="_blank">marc.millas@mokadb.com</a>&gt; wrote<br> &gt;&gt; Too my \
understanding it looks like the parser did not parse the select<br> &gt;&gt; distinct \
as we think he does.<br> <br>
&gt; The DISTINCT clause doesn&#39;t really come into play here at all, so if you<br>
&gt; think it does you indeed have a misunderstanding.<br>
<br>
No, he&#39;s correct:<br>
<br>
postgres=# create table z (f1 int);<br>
CREATE TABLE<br>
postgres=# insert into z values(null);<br>
INSERT 0 1<br>
postgres=# insert into z select null;<br>
INSERT 0 1<br>
postgres=# insert into z select distinct null;<br>
ERROR:   column &quot;f1&quot; is of type integer but expression is of type text<br>
LINE 1: insert into z select distinct null;<br>
                                                         ^<br>
HINT:   You will need to rewrite or cast the expression.<br>
<br>
The first two INSERTs are accepted because there&#39;s nothing<br>
&quot;between&quot; the untyped NULL and the INSERT, so we can resolve<br>
the NULL as being of type int.   But use of DISTINCT requires<br>
resolving the type of the value (else how do you know what&#39;s<br>
distinct from what?) and by default we&#39;ll resolve to text,<br>
and then that doesn&#39;t match what the INSERT needs.<br>
<br>
                                    regards, tom lane<br>
</blockquote></div>



[prev in list] [next in list] [prev in thread] [next in thread]