I need to write a Postgres script to loop over all the rows I have in a particular table, get the date column in that row, find all rows in a different table that have that date and assign that row the ID of the first row in its foreign key column. Explaining further, my data (simplified) looks like:
first_table:
id INT, run_date DATE
second table:
id INT, target_date TIMESTAMP, first_table_id INT
pseudocode:
first_table_array = SELECT * FROM first_table;
i, val = range first_table_array {
UPDATE second_table SET first_table_id={val.id} WHERE date={val.run_date}
}
But it's not actually that simple, because I'm comparing a date against a timestamp (which I presume will cause some problems?), and I need to refine the time period as well.
So instead of doing where date={val.run_date} I actually need to check whether the date is run_date starting at 4am, and going into 4am the following day.
So say run_date is 01/01/2015, I need to convert that value into a timestamp of 01/01/2015 04:00:00, and then create another timestamp of 01/02/2015 04:00:00, and do:
where date > first_date AND date < second_date.
So, something like this:
pseudocode:
first_table_array = SELECT * FROM first_table;
i, val = range first_table_array {
first_date = timestamp(val.run_date) + 4 hrs
second_date = timestamp(val.run_date + 1 day) + 4 hrs
UPDATE second_table SET first_table_id={val.id} WHERE date > first_date AND date < second_date
}
Is postgres capable of this sort of thing, or am I better off writing a script to do this? Do I need to use loops, or could I accomplish this stuff just using joins and standard SQL queries?
