PostgreSQL group by all fields

Question

I have a query like this:

SELECT
table1.*,
sum(table2.amount) as totalamount
FROM table1
join table2 on table1.key = table2.key
GROUP BY table1.*;

I got the error: column "table1.key" must appear in the GROUP BY clause or be used in an aggregate function.
Are there any way to group "all" field?

Answer 1

There is no shortcut syntax for grouping by all columns, but it's probably not necessary in the described case. If the key column is a primary key, it's enough when you use it:

GROUP BY table1.key;

Answer 2

You have to specify all the column names in group by that are selected and are not part of aggregate function ( SUM/COUNT etc)

select c1,c2,c4,sum(c3) FROM  totalamount
group by c1,c2,c4;

A shortcut to avoid writing the columns again in group by would be to specify them as numbers.

select c1,c2,c4,sum(c3) FROM  t
group by 1,2,3;

Answer 3

I found another way to solve, not perfect but maybe it's useful:

SELECT string_agg(column_name::character varying, ',') as columns
FROM information_schema.columns
WHERE table_schema = 'your_schema'
AND table_name   = 'your_table

Then apply this select result to main query like this:

$columns = $result[0]["columns"];

SELECT
table1.*,
sum(table2.amount) as totalamount
FROM table1
join table2 on table1.key = table2.key
GROUP BY $columns;