Postgresql – Prioritised table query

postgresql

I'm looking for a way to query two tables at once but …

I want the query to first run against Table A and then if Table A returns no matching records to query Table B. There many well be instances of both Table A and B containing the same data but as far as I am concerned Table A for any duplicates would be regarded as the master data.

My problem is that I don't know if this is possible and if it is what the name of this sort of query is called, so I've not be able to first hit Google to get some background reading done.

Best Answer

Since Postgres is always evaluating CTEs, you can use a CTE for table a and then something like this:

WITH a AS
  ( SELECT <a column list>
    FROM table_a
    WHERE <conditions>
  )

TABLE a 

UNION ALL

SELECT <b column list>
FROM table_b
WHERE NOT EXISTS (TABLE a) 
  AND <maybe other conditions> ;

Note: (TABLE a) is just a synonym of (SELECT * FROM a)