Re: Targetlist lost when CTE join <targetlist lost when CTE join>

HI,

On Jun 28, 2023, 17:26 +0800, Julien Rouhaud <rjuju123(at)gmail(dot)com>, wrote:
> On Wed, Jun 28, 2023 at 05:17:14PM +0800, Julien Rouhaud wrote:
> > >
> > > Table t1 and  t2 both has 2 columns: c1, c2, when CTE join select *, the result target list seems to lost one’s column c1.
> > > But it looks good when select cte1.* and t1.* explicitly .
> > >
> > > Is it a bug?
> >
> > This is working as intended. When using a USING clause you "merge" both
> > columns so the final target list only contain one version of the merged
> > columns, which doesn't happen if you use e.g. ON instead. I'm assuming that
> > what the SQL standard says, but I don't have a copy to confirm.
>
> I forgot to mention that this is actually documented:
>
> https://www.postgresql.org/docs/current/queries-table-expressions.html
>
> Furthermore, the output of JOIN USING suppresses redundant columns: there is no
> need to print both of the matched columns, since they must have equal values.
> While JOIN ON produces all columns from T1 followed by all columns from T2,
> JOIN USING produces one output column for each of the listed column pairs (in
> the listed order), followed by any remaining columns from T1, followed by any
> remaining columns from T2.

Thanks for your help.

Regards,
Zhang Mingli