I have two tables. A transaction table that stores prices at specific dates (with precise timestamps). And a price-history table where we have one row for every day.
I now want to put the price from the transaction table in the price-history table where the timestamps match (same day). The below photo should help to clarify what I need. The price column in the price-history table is the needed result.
Add a comment
|
2 Answers
A simple join should work here:
SELECT t.date, ph.price
FROM Transactions t
INNER JOIN "price-history" ph
ON ph.date = t.date::date
This assumes that the price-history table is like a calendar table, and has data for every date of interest. If not, then we should modify the above to use a left join, and also we should select COALESCE(ph.price, 0) instead of just ph.price.
1 Comment
Luke
Good hint with the condition for the calendar-like table.
To update the table, you would use:
update price_history ph
set price = t.price
from transactions t
where t.date::date = ph.date;
Note: If there are two transactions on the same date, an arbitrary one is used for the update.
If you need to construct the price history table, you might construct it first with 0 values and then update:
create table price_history (
date date primary key,
price numeric(10, 2) -- or whatever
);
insert into price_history (date, price)
select gs.dte, 0
from generate_series('2021-01-01'::date, '2021-12-31'::date, interval '1 day') gs(dte);
And then update the values as above.
