Re: AWS forcing PG upgrade from v9.6 a disaster

"Dean Gibson (DB Administrator)" <postgresql@xxxxxxxxxxx> · Sun, 30 May 2021 22:24:00 -0700

    On 2021-05-30 21:44, Tom Lane wrote:

      "Dean Gibson (DB Administrator)" <postgresql@xxxxxxxxxxx> 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)?