Postgresql Mirror a table schema in another table



  • 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.



  • You can use https://www.postgresql.org/docs/13/ddl-inherit.html

    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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2