Home Combine queries to multiple tables having the same columns in to one query

# Combine queries to multiple tables having the same columns in to one query

Marko Taht
1#
Marko Taht Published in 2017-12-05 11:46:15Z
 I have 6 tables. All of them can have millions and millions of lines of data. All of the tables have object_id, changer_id, date_created columns. I need to make a query on all of the tables SELECT object_id FROM table# WHERE changer_id=someId AND date_created > dateA AND dateCreated < dateB  or its grails equivalent table#.createQuery().list{ projections{property('object_id')} eq('changer_id', someId) ge('dateCreated' dateA) le('dateCreated', dateB) }  Same query will be made on all of the 6 tables, and then the results are combined and all duplicates are removed. Im not using between because dateB might not always exist so it would be ommited. Is there a way to combine this query into single query over 6 tables or any other way to make it faster than 6 separate queryes?
Jasen
2#
 SQL inheritance: assuming your tables are table1, table2, etc, setup: do this once: create table combined ( like table1 ); alter table table1 inherits combined; alter table table2 inherits combined; alter table table3 inherits combined; alter table table4 inherits combined; alter table table5 inherits combined; alter table table6 inherits combined;  the query, do this many times as needed: SELECT object_id FROM combined WHERE changer_id=someId AND date_created > dateA AND dateCreated < dateB  postgresql will internally generate the unions needed. I'm assuming that all the tables have the same columns, if not exclude any columns that are missing from some tables when you create the combined table. (you don't have to use like... , you can use the more common list-of-columns syntax)