Thread: running a query on a list of tables
I am trying to figure out how to run a query on a list of tables or views in this case.
I can run :
I can run :
SELECT * FROM tablelist;
which returns a list of tables
names
----------------
mortgage
creditcard
auto
........continued
I want to run :
SELECT * FROM (SELECT * FROM tablelist);
all tables have the same columns and types.
any idea how to make this work? tablelist contains a large number of tables that will change over time and I do not want to manually write a bunch of unions.
any suggestions?
Thank you
On 29 October 2018 at 16:48, Mark King <fires10@gmail.com> wrote: > SELECT * FROM tablelist; > > which returns a list of tables > > names > ---------------- > mortgage > creditcard > auto > ........continued > > > I want to run : > > SELECT * FROM (SELECT * FROM tablelist); > > > all tables have the same columns and types. > > any idea how to make this work? tablelist contains a large number of tables > that will change over time and I do not want to manually write a bunch of > unions. > > any suggestions? You could just create a parent table then alter all of your tables in tablelist to INHERIT the parent. You can then simply; SELECT * FROM theparent; The query planner will automatically combine the results from each inheriting table. Documentation in: https://www.postgresql.org/docs/11/static/ddl-inherit.html -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services