Question 421972 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Specify in GROUP BY that the column used is the one in SELECT statement

I am trying to select data from two tables A and B (which have more or less the same column), and create a third table C which will also contain similar columns

Something like this:

CREATE TABLE C as (
    SELECT COALESCE(DATE(A.event_datetime), B.event_date) as event_date,
        SUM(COALESCE(A.sale_value, B.sale_value)) as sale_value
    FROM A
    INNER JOIN B
        ON A.join_key = B.join_key
    GROUP BY event_date

The above statement does not work, because event_date is ambiguous – it can mean B.event_date or the resulting event_date of the SELECT statement.

I know I can simply use the position of the column in the SELECT statement:

GROUP BY 1

But I am not totallly satisfied by it because the actual query is much more complex and has many more columns in the GROUP BY clause, making it far less readable.

I there a way to lift the ambiguity that I want to use the columns in the SELECT statement for my group by, without:

  • Using positional arguments
  • Renaming the columns

?

Tags:

2

Answers


  1. 0

    My recommendation is to be explicit and write

    GROUP BY COALESCE(DATE(A.event_datetime), B.event_date)
    Login or Signup to reply.
  2. 0

    As for the actual question, @LaurenzAlbe gave a clear and unambiguous answer.
    To improve readability, you can use framing of complex expressions. Perhaps, the actual query execution plan is likely to remain the same.

    CREATE TABLE C as (
  select event_date,sum(sale_value) as sale_value
  from(
    SELECT COALESCE(DATE(A.event_datetime), B.event_date) as event_date,
        COALESCE(A.sale_value, B.sale_value) as sale_value
    FROM A
    INNER JOIN B
        ON A.join_key = B.join_key
   )x
  GROUP BY event_date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search