SQL: preserve original column name from CASE + GROUP BY (+ VIEW) - ambiguous column name

Question

How to preserve original column name when I use CASE to get column from many tables based on condition and I have to use this name in GROUP BY because used aggregate function? I need it for create VIEW with proper column names. I use PostgreSQL.

Example

I have three tables:

create table first
(
    id    bigserial primary key,
    field varchar(255) not null
);

create table second
(
    id       bigserial primary key,
    field    varchar(255) not null,
    first_id bigint constraint second_first_id references first
);

create table values
(
    id       bigserial primary key,
    value    numeric,
    first_id bigint constraint value_first_id references first
);

Some input data:

insert into first (field) values ('aaa');
insert into first (field) values ('bbb');
insert into second (field, first_id) values ('ggg', 1);
insert into second (field, first_id) values ('hhh', 2);
insert into values (value, first_id) values (2, 1);
insert into values (value, first_id) values (4, 1);
insert into values (value, first_id) values (20, 2);
insert into values (value, first_id) values (40, 2);

And SQL that join those tables and group by:

select sum(v.value) as sum_value,
       f.field,
       s.field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by f.field, s.field;

Now I add CASE statement with some condition (true for simplicity) to get field field from first or second table:

select sum(v.value) as sum_value,
       case
           when true then
               f.field
           else
               s.field
           end      as field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by field;

There is error:

ERROR: column reference "field" is ambiguous

I could fix the error by change as field and group by field with new name form example field1.

But I want to preserve original name field. Its important when I create view:

create or replace view my_view as
select sum(v.value) as sum_value,
       case
           when true then
               f.field
           else
               s.field
           end      as field1
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by field1;

select * from my_view;

The view returns column with name field1 instead of field.

I try to qualify name as field in view by group by my_view.field but it also doesn't works:

ERROR: missing FROM-clause entry for table "my_view"

I can copy whole CASE statement and paste it in GROUP BY but I think it's not a good solution (especially when CASE is very long and many columns):

create or replace view my_view as
select sum(v.value) as sum_value,
       case
           when false then
               f.field
           else
               s.field
           end      as field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by case
             when false then
                 f.field
             else
                 s.field
             end;

I saw those questions: 1, 2, it is not a duplicate.

Maybe `create or replace view my_view(sum_value, field) as`? — lemon, May 29 '23 at 16:53
Thanks @lemon! Using temporary name in select `as field1` and final name `field` in view definition is nice. It solves my specific problem (because I use view), but I have to copy all names to view definition. And also I was hoping to find more generic solution (without view, only select) - some way to qualify the name or double naming with `as`, but I don't know it is possible. — mkczyk, May 29 '23 at 17:01
Note that tables have _columns_, not fields. And you're using a case _expression_, not statement. — jarlh, May 29 '23 at 19:31
You can use a sub-select or CTE to run aggregation on outer query. — Parfait, May 29 '23 at 20:55

score 5 · Answer 1 · answered May 29 '23 at 17:04

You could use the ordinal of the column in the group by. ie:

select sum(v.value) as sum_value,
       case
           when true then
               f.field
           else
               s.field
           end      as field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by 2;

SQL: preserve original column name from CASE + GROUP BY (+ VIEW) - ambiguous column name

Example

1 Answers1