Postgres: get total number of matched records using UNION I'm running a paginated SELECT ... UNION query, and I need to determine the number of pages that are in the resultset. For example, consider the query:

(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT 3


When I get the following resultset, I can determine the total number of matched records:

| id | type   | matches |
|  1 | table1 |      12 |
|  2 | table1 |      12 |
|  1 | table2 |     150 |


Summing the distinct matches columns will then give me the total number of records returned, 162. But then if my current LIMIT/OFFSET doesn't give me any records from table2, I can no longer tell how many pages of data to expect:

 | id | type   | matches |
 |  1 | table1 |      12 |
 |  2 | table1 |      12 |
 |  3 | table1 |      12 |


How can I find the total number of matched records from all clauses in a UNION even if not all clauses return records within the current LIMIT/OFFSET scope?
I think you just need to change your query...

(SELECT
  NULL AS id,
  'allTables' AS type,
  (SELECT COUNT(*) OVER() FROM table1) + (SELECT COUNT(*) OVER() FROM table2) AS matches)
UNION
(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT (1 + 3)


But I can't speak to how efficient it will be....

-- Additional

This may work, as well.  But again, efficiency will likely be poor (since it will get all results, regardless if you use it or not)

WITH results as (
  (SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
  UNION
  (SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
) SELECT NULL AS id, 'allTables' AS type, COUNT(matches) FROM results
UNION
SELECT id, type, matches FROM results
LIMIT (1 + 3)


Not very friendly to the DB, in either case.  But you only have to have your WHERE clause twice (as opposed to 4 times)