Take daily snapshot of table in Postgresql and append it to another table

I have a table in Postgresql which stores product information and it's price. The problem is, as price of the product changes, the prices are updated in existing rows, so I do not have the historical data. I would like to take a snapshot of this table every night at 12:00 and store this data in another table so that I can find out change in price everyday.

The postgresql database is hosted in AWS. I have to write a node.js script which does this task. You can assume the Table structure to be like:

Product	Price
Prod_1	20
Prod_2	25

The result should be:

Produce	Price	Snapshot at
Prod_1	20	2021-01-22
Prod_2	25	2021-01-22
Prod_1	22	2021-01-23
Prod_2	21	2021-01-23

Thanks

https://stackoverflow.com/questions/65837680/take-daily-snapshot-of-table-in-postgresql-and-append-it-to-another-table January 22, 2021 at 08:25AM