PostgreSQL requires "GROUP BY" when aliasing a table?
The problem is that the 2 tables are not identical.

The first has (id) as the PRIMARY KEY while the second has (id) a UNIQUE constraint (or index, doesn't matter). This might seem like a minor detail but it isn't.

Postgres has added in version 9.1, a feature were functionally dependent columns need not be mentioned in a GROUP BY clause (if the column they depend on is mentioned in the GROUP BY) and still used in the HAVING, SELECT and ORDER BY clauses. However the implementation is not 100% complete. It doesn't identify all possible functional dependencies but only those coming from PRIMARY KEY constraints. While a UNIQUE constraint with all columns NOT NULL does not differ in any meaningful way from a PRIMARY KEY constraint, the implementation does not consider UNIQUE constraints.

So, for example, with these two very similar tables (test in SQLfiddle):

CREATE TABLE products
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    PRIMARY KEY (id)
  ) ;

CREATE TABLE products_active
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    UNIQUE (id)
  ) ;


The query will succeed for the first table:

SELECT p.id, p.status, p.name
FROM products AS p
GROUP BY p.id ;


while it will fail for the second:

SELECT p.id, p.status, p.name
FROM products_active AS p
GROUP BY p.id ;


with:


  ERROR: column "p.status" must appear in the GROUP BY clause or be used in an aggregate function