1

I'm trying to run the same query over multiple tables in my Postgres database, that all have the same schema.

This question: Select from multiple tables without a join? shows that this is possible, however they are hard-coding the set of tables.

I have another query that returns the five specific tables I would like my main query to run on. How can I go about using the result of this with the UNION approach?

In short, I want my query to see the five specific tables (determined by the outcome of another query) as one large table when it runs the query.

I understand that in many cases similar to my scenario you'd simply just want to merge the tables. I can not do this.

Community
  • 1
  • 1
Azarantara
  • 517
  • 3
  • 19

1 Answers1

0

One way of doing this that may satisfy your constraints is using table inheritance. In short, you will need to create a parent table with the same schema, and for each child you want to query you must ALTER that_table INHERIT parent_table. Any queries against the parent table will query all of the child tables. If you need to query different tables in different circumstances, I think the best way would be to add a column named type or some such, and query only certain values of that table.

Ramfjord
  • 872
  • 8
  • 14
  • I do have a parent table with the same schema that is empty, as I was thinking along these lines.In your `ALTER that_table INHERIT parent_table` code, how would you insert the result of the query that determines which sub tables to run on? – Azarantara Jul 24 '15 at 08:47
  • As I mentioned above, I wouldn't query a list of tables per say, but I would add a ``type`` column to each table that was check constrained to a single value. Then I would query only certain types at a time, which would effectively query certain tables. I would not recommend querying the pg internal tables (pg_class etc.) to get the table names, because they are likely to be subject to change when you upgrade your postgres version. – Ramfjord Jul 24 '15 at 15:59