Using GROUP BY with UNION in a PostgreSQL query.

The syntax for using GROUP BY in Postgres with a UNION can be confusing at first and took me a few tries to get right.

Incorrect

SELECT id FROM table_one UNION SELECT id from table_two GROUP BY table_one.id

Correct

SELECT * FROM (SELECT id as id_1 FROM table_one UNION SELECT id as id_2 from table_two) subquery GROUP BY subquery.id_1

As you can see, you will need to create a subquery, select each column from that subquery, and then you can group by in the parent query.