postgresql querying on multiple identical tables

Question

Hello I have several databases, such as mytable_2009_11_19_03 where last 2 numbers identify the hour (table for 03:00), now I want to query something from _00 to _23 . It could be done in such way but it is really clumsy

select * from mytable_2009_11_19_00 where type = 15
UNION
select * from mytable_2009_11_19_01 where type = 15
UNION
...........
select * from mytable_2009_11_19_23 where type = 15

How could I do it easier? regards

Answer 1

The easiest solution would likely be to build a view of all of the tables, then you can query them easily. You could easily write a procedure to generate the view. Also, if you use "union all", it will be faster, if the result you want is all of the rows (as opposed to distinct rows) and you can still grab distinct rows by selecting distinct from the view if you need it sometimes.

Answer 2

If you know that all the tables generated are always identical in schema, in PostgreSQL you could create a parent table and set the newly created tables to INHERIT from the parent.

CREATE TABLE mytable_2009_11_19 (LIKE mytable_2009_11_19_00);
ALTER TABLE mytable_2009_11_19_00 INHERIT mytable_2009_11_19;
ALTER TABLE mytable_2009_11_19_01 INHERIT mytable_2009_11_19;
.
.
.
ALTER TABLE mytable_2009_11_19_23 INHERIT mytable_2009_11_19;

SELECT * FROM mytable_2009_11_19 where type = 15;

This is similar to using a view, but there are differences (listing the CONs first):

• (CON) This method requires you to be able to ALTER the individual tables, which you may not have the rights to do. A VIEW does not require this level of access.
• (CON) This method requires the tables to all have the same structure or, at the very least, the parent must have ONLY the common elements between all the children.
• (PRO) To add tables to a VIEW, you have to drop and redefine the VIEW (and it's permissions, if necessary). With a parent table, it's easy to add or remove tables by ALTERing each one with INHERIT/NOINHERIT.
• (PRO) If your schema contains fields like date and timestamp and the structure rarely changes, you can build a single parent table and use INHERIT/NOINHERIT on a rolling basis to provide a time "window" that you can query against without having to query the entire history.

Answer 3

This table structure sounds like it was intended to be part of a data partitioning scheme. This is not bad design. This is a very good thing!

Time based data like this is always being added to in the front and dropped off the back as it expires. Using one huge table would result in large amounts of index fragmentation as the data updates and in very large maintenance times for operations like VACUUM.

If you follow the link I included in the first paragraph and read all about partitioning, you'll be able to use CHECK constraints to make date searches very fast. Any SELECT that includes a WHERE timestamp > x AND timestamp < y will be quick.

Now, if these tables don't include any timestamps then the partitioning with CHECK constraints won't work and you will just have to write scripts to write the clumsy UNION queries for you.