agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: agharta82@gmail.com <agharta82@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Group by column alias where same-column-name already exists
Date: Tue, 11 Dec 2018 17:39:51 +0100
Message-ID: <70aee131-8353-607e-0eff-6fb2b9928cf6@gmail.com> (raw)

Hi all,

A little question about grouping by a computed column alias where other 
columns with same name exists.

Take look at this query (don't take care about, it was created as a test 
to explain my question).

select case when (second_table.c1 = 'X') then '1' else '2' end as c1,
first_table.c2
from (
     select 'A'::text as c1, 'B'::text as c2
) first_table
inner join (
     select 'X'::text as c1
     union
     select 'W'::text as c1
     union
     select 'X'::text as c1
)  second_table on (true)
group by c1, first_table.c2


I have a computed coulmn alias in select called c1 (select case when 
(second_table.c1 = 'X') then '1' else '2' end as c1)

I want to group by that alias c1 (group by c1)

BUT first_table has a c1 column and second_table has a c1 column too!

If i run the query it returns "ERROR: column reference "c1" is ambiguous"

Someone knows a way to solve this?

Not replacing grou by alias with its case and without changing column 
names, oblivious.

In other dbs (like firebird) main (computed ) select column alias name 
takes precedence in group by clause. So if i group by c1 that means the 
computed (case when.... ) c1 in that case.


Best regards,

Agharta

view thread (3+ messages)  latest in thread

Message-ID: <70aee131-8353-607e-0eff-6fb2b9928cf6@gmail.com>
Permalink:  ../70aee131-8353-607e-0eff-6fb2b9928cf6@gmail.com/
Also on:    postgresql.org/message-id/70aee131-8353-607e-0eff-6fb2b9928cf6@gmail.com

reply

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-sql@postgresql.org
  Cc: agharta82@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Group by column alias where same-column-name already exists
  In-Reply-To: <70aee131-8353-607e-0eff-6fb2b9928cf6@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox