From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-performance@postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Sun, 30 May 2021 22:24:00 -0700
Message-ID: <6e47da10-5eb2-c54c-c77e-f27ba04e094b@mailpen.com> (raw)
In-Reply-To: <2524029.1622436267@sss.pgh.pa.us>
References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
<92cc8563-195f-8727-d887-5f2bb63f231f@mailpen.com>
<2370D2C6-4D91-4C96-9410-11EFD9954B21@thebuild.com>
<5463af86-9007-1094-78bf-18e54c60edaf@mailpen.com>
<2524029.1622436267@sss.pgh.pa.us>
On 2021-05-30 21:44, Tom Lane wrote:
> "Dean Gibson (DB Administrator)" <postgresql@mailpen.com> writes:
>> I thought that having a "USING" clause, was semantically equivalent to
>> an "ON" clause with the equalities explicitly stated. So no, I didn't
>> try that.
> USING is not that, or at least not only that ... read the manual.
>
> I'm wondering if what you saw is some side-effect of the aliasing
> that USING does.
>
> regards, tom lane
/|USING ( /|join_column|/ [, ...] )|/
/A clause of the form //|USING ( a, b, ... )|//is shorthand for
//|ON left_table.a = right_table.a AND left_table.b =
right_table.b ...|//. Also, //|USING|//implies that only one of
each pair of equivalent columns will be included in the join
output, not both./
/
/
/The //|USING|//clause is a shorthand that allows you to take
advantage of the specific situation where both sides of the join use
the same name for the joining column(s). It takes a comma-separated
list of the shared column names and forms a join condition that
includes an equality comparison for each one. For example, joining
//|T1|//and //|T2|//with //|USING (a, b)|//produces the join
condition //|ON /|T1|/.a = /|T2|/.a AND /|T1|/.b = /|T2|/.b|//./
/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|//./
/Finally, //|NATURAL|//is a shorthand form of //|USING|//: it forms
a //|USING|//list consisting of all column names that appear in both
input tables. As with //|USING|//, these columns appear only once in
the output table. If there are no common column names, //|NATURAL
JOIN|//behaves like //|JOIN ... ON TRUE|//, producing a
cross-product join./
I get that it's like NATURAL, in that only one column is included. Is
there some other side-effect? Is the fact that I was using a LEFT JOIN,
relevant? Is what I was doing, unusual (or risky)?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgresql@mailpen.com
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <6e47da10-5eb2-c54c-c77e-f27ba04e094b@mailpen.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox