PostgreSQL: how to keep duplicate records in the query result of UNION operator

Question

When I combine the results of multiple queries into a single result using the UNION operator, I do not see duplicate records in the result. I need to keep the duplicate records in the query output, how to keep them?

Answer 1

If you want to keep duplicate records in the result, you need to use UNION ALL instead of UNION. By defaults, UNION operator removes all duplicate records.

E.g.

SELECT a,b,c FROM table1

UNION ALL

SELECT a,b,c FROM table2