Today I Learned

A Hashrocket project

Difference between `union` and `union all` in psql

union is an operator that operates on two sets of results, combining them:

chris=# select 1 union select 2;
 ?column?
----------
        1
        2
(2 rows)

It filters duplicate results though:

chris=# select 1 union select 1;
 ?column?
----------
        1
(1 row)

If you want to not worry about duplicate results and just stack the result sets on top of each other use union all:

chris=# select 1 union all select 1;
 ?column?
----------
        1
        1
(2 rows)
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.