postgres: Querying across rows in multiple tables?

Question

Is there a way to apply a single query across the concatenated rows of multiple tables? I have several tables storing metrics data, where data is initially collected in 10 second intervals, but is periodically rolled up into 1 minute intervals in another table and ultimately into 10 minute intervals in a third table.

That is, I want to be able to do something like:

SELECT value FROM table1 + table2 + table3
  WHERE age(current_timestamp, time) > '2 days'
    AND metrics_name = 'foo';

I don't think a JOIN operation is the right solution here. It looks like I can get the result I want using the UNION operator, which would look something like:

SELECT value from TABLE1 ...
  UNION SELECT value FROM table2 ...
  UNION SELECT value from TABLE3 ...

Where ... is the contents of my WHERE clause (etc), but this gets messy very quickly if the individual SELECT queries are complicated (and fails the "don't repeat yourself" mantra). Is there a different way of cracking this particular nut?

using union all is possibly an option too, but you don't give us much detail to work with — Paul Maxwell
– Paul Maxwell, Commented Dec 5, 2017 at 2:17
What kind of additional details would help out? I'm trying to avoid having to repeat a query multiple times when selecting across the concatenated results from multiple tables. I was hoping the above was sufficient to illustrate the use case, but I'm happy to work up an extended example if you think that would help. — larsks
– larsks, Commented Dec 5, 2017 at 2:20

Alan · Accepted Answer · 2017-12-05 04:05:47Z

1

Use a nested query with the UNION in the nested quer. And I highly recommend you use UNION ALL unless you want to eliminate duplicates, which you won't in this case, so use UNION ALL.

SELECT
    value
FROM (
    SELECT value, age, metrics_name FROM Table1
    UNION ALL SELECT value age, metrics_name FROM Table2
    UNION ALL SELECT value age, metrics_name FROM Table3
) AS All_Metrics
WHERE
    All_Metrics.age(current_timestamp, time) > '2 days'
    AND All_Metrics.metrics_name = 'foo';

answered Dec 5, 2017 at 4:05

Alan

1,4282 gold badges20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres: Querying across rows in multiple tables?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related