Query multiple summary tables in the same select

Question

I have several monthly summary tables and I need to query all of them in the same report. How would I go about doing that? Below are a few ideas that I had that are not working.

aug_summary

sept_summary

oct_summary

`SELECT name, timestamp FROM aug_summary AND sept_summary AND oct_summary;`

?

`select * from '*_summary'`

?

Are name, timestamp columns in all three tables? If so you can use a UNION — Raymond Nijland, Feb 05 '18 at 15:12
removed the mysql tag. thanks @RaymondNijland. They will have identical formats so I think a UNION will work. Is there a way to UNION *? — hhh_, Feb 05 '18 at 15:15
Having one table for each month seems like a bad design to begin with. You should only have a single table with a column that tells for which month the summary is. You can always create a view that UNIONs all the tables. That way you only need to do it in one place. — , Feb 05 '18 at 15:20
@a_horse_with_no_name, the tables are actually named by month. summary201711, summary_201712... etc. — hhh_, Feb 05 '18 at 15:27
@a_horse_with_no_name true but PostgreSQL doesn't implement ideal partitioning (atleast not until version 10)... partitioning might be the reason behide his. — Raymond Nijland, Feb 05 '18 at 15:34
@RaymondNijland: for something like that, even the inheritance based partitioning would work just fine — , Feb 05 '18 at 15:36

score 2 · Accepted Answer · 2018-02-05T15:49:15.717

You have several options to deal with this problem.

The obvious one is to normalize your database and merge all the data into single table that contains a "month" column, identifying the month for which the data is.

This looks like some manually created "partitioning" scheme. So another choice you have is to convert that into a table that uses Postgres' built-in partitioning

As a quick fix, you could simply create view that does a union on all the tables, that way you have that huge UNION only once in your database and you can use a simple select * from summary_table in your report.

The report job then doesn't need to be changed. You only change the view once you create a new summary table (something you also wouldn't need to do with a normalized model or partitioning)

You can create a function that loops through the available tables and returns the contents of each table:

create function get_summary()
  returns setof summary201711
as
$$
declare 
  l_sql text;
  l_rec record;
begin
  for l_rec in select schemaname, tablename 
               from pg_tables 
               where schemaname = 'public'
               and tablename like 'summary%' --<< adjust this to match your pattern
  loop
     l_sql = format('select * from %I.%I', l_rec.schemaname, l_rec.tablename);
     return query execute l_sql;
  end loop;
end;
$$
language plpgsql;

Which of the tables you use for the returns setof summary201711 doesn't really matter as they all have to have an identical structure.

Once you have that function you can use it like this:

select *
from get_summary();

Not sure how fast that is going to be though. My guess is that a view (or any form of partitioning) will be faster.

Thank you. I also consulted with a colleague and they recommended creating a view. Appreciate the assistance! — hhh_, Feb 05 '18 at 15:56
A really good answer, especially because you explain how the tables should be stored (partitioning) and then give a clever solution to the problem. — Gordon Linoff, Feb 05 '18 at 16:44

score 0 · Answer 2 · answered Feb 05 '18 at 15:12

0

SELECT name, timestamp FROM aug_summary
 UNION
SELECT name, timestamp FROM sept_summary
 UNION
SELECT name, timestamp FROM oct_summary

Etc, etc.

answered Feb 05 '18 at 15:12

OTTA

1,071
7
8

is there a way to union *? otherwise, I would have to update the job every month – hhh_ Feb 05 '18 at 15:14
Not that I'm aware of. – OTTA Feb 05 '18 at 15:18

Query multiple summary tables in the same select

2 Answers2