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.

Answer 1

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;