Re: joined tables with USING and GROUPBY on the USING() column

On 09/07/2016 12:53 PM, Geoff Winkless wrote:
> I'll start by saying that I'm sure I'm missing something obvious...
>
> I have a query that is working fine on all my servers except one. The
> only obvious difference is that the failing one is running 9.5.3,
> while most are running 9.5.4, but since the query works on a 9.5.1 box
> I also have I can't imagine that's the cause.
>
> I can simplify the query (and still retain the error) down to:
>
> SELECT results.token FROM results INNER JOIN tokens USING(token) GROUP BY token;
>
> ERROR: column "results.token" must appear in the GROUP BY clause or be
> used in an aggregate function

Seems to me Postgres is not seeing results.token as the same as token.

What are schema for the tables results and tokens?

What happens if you table qualify all the references to token?

>
> All well and good, and I'm happy enough to change the query's GROUP BY
> to include the table name, but it's confusing me how it works OK on
> all servers except one.
>
> Is there some configuration option I'm missing?
>
> Thanks!
>
> Geoff
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com