Postgresql Mirror a table schema in another table

Question

I have the following table asset_historical_data in a postgresql database with historical minutely asset data.

timestamp	asset_ticker	asset_price	asset_market_cap_bn
02/28/2022 10:00:00	ABC	7.77	1.01342
02/28/2022 10:00:00	XYZ	10.03	2.12233
02/28/2022 10:01:00	ABC	8.77	1.71342
02/28/2022 10:01:00	XYZ	10.05	2.13233

However, this table is quite large and in production we only need hourly asset data. I don't wanna drop any data that we collect. So, I am thinking of creating a new table called asset_historical_data_filtered which will only have hourly asset data like follows:

timestamp	asset_ticker	asset_price	asset_market_cap_bn
02/28/2022 10:00:00	ABC	7.77	1.01342
02/28/2022 10:00:00	XYZ	10.03	2.12233

I want this new table to mirror the schema of the asset_historical_data table. In particular, I want any schema changes in the asset_historical_data table (such as addition of a column) to be reflected on the asset_historical_data_filtered table. This is necessary cause both tables essentially contain the same data but with different granularity. Is that possible in postgresql?

I use alembic to manage schema revisions, so a solution with alembic will be preferred.

Answer 1

You can use Inheritance

create table asset_historical_data_filtered ()
 INHERITS (asset_historical_data);

Note that a query on the parent table will include records from children. If you don't want this, some possible options are:

1. Use the ONLY keyword when query the parent

   select * from only asset_historical_data;
   

2. Make a base parent table, both asset_historical_data and asset_historical_data_filtered inherit from it.

3. Insert the data generated in 0th minute and other times into different tables.

Please note the following limitation (copied from official documentation)

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint.