GROUP BY all columns of a subtable without enumerating them

Question

Is there a way to group by all columns in a subtable without enumerating them all? e.g. Suppose I wanted to query something like:

SELECT t1.col_1, t1.col_2, ... , t1.col_n, t2.col_k, COUNT(*)
    FROM t1, t2
    WHERE t2.col1>0
      AND t2.col1=t1.col1
    GROUP BY t1.col_1, t1.col_2, ... , t1.col_n, t2.col_k

Writing out all these column names (moreover twice) is verbose if n is large. I am wondering if there is a standard language construct to do this. E.g. a key word COLUMNS used like:

 SELECT COLUMNS(t1), t2.col_k, COUNT(*)
   FROM ...
   ...
   GROUP BY COLUMNS(t1), t2.col_k

(Tag included for Postgres as I am interested in a Postgres solution if no SQL standard exists.)

Answer 1

You can use t1.* in the SELECT list. That's expanded to all columns of t1. Exactly what you ask for.

The same syntax t1.* is not allowed in the GROUP BY list. But you can place the primary key column(s) of t1 there to cover all columns of the same table. Almost what you ask for.

SELECT t1.*, t2.col_k, COUNT(*) AS my_count
FROM   t1 
JOIN   t2 ON t2.col1 = t1.col1
WHERE  t2.col1 > 0
GROUP  BY t1.pk_column, t2.col_k;

This works since Postgres 9.1, and complies to standard SQL. See:

• PostgreSQL - GROUP BY clause

The SQL standard would allow to cover more functional dependencies in similar fashion (like UNIQUE columns), but that's not implemented in Postgres.