How to insert into a table daily automatically in postgresql?

Question

I'm trying to create a log of an inventory that runs daily

Basically on my log table i want to sum how many units have status1, how many units have status2, etc for every day

is there a way to fill up my log table without having to do it manually every day?

My log table is id serial, date date, count_of_status1 integer, count_of_status2 integer

My insert query would be

insert into logtable(date, count_of_status1, count_of_status2)
select now(), count(*) filter (where status =1), count(*) filter (where status = 2)
from mytable

How to make this insert to be done daily in an automated form rather than manually?

Answer 1

PostgreSQL does not have a built in job scheduler. There are many options to run jobs on a schedule. If you already have a generic job scheduler in use in your IT environment, I'd recommend making use of that, rather than installing and configuring and learning a new one just for PostgreSQL. For example, I use "cron" for all my job scheduling outside of PostgreSQL, so I use it for PostgreSQL as well, using something like psql -f job.sql as the command to run. You will have to arrange for the job to be able to connect to the database without a password--there are several ways to do that depending on your security environment.

For the query itself, you can't expect the job to run at exactly midnight down to the nanosecond. So run the job several minutes after midnight (to make sure clock skew doesn't cause it to accidentally run before midnight), and put WHERE clauses in the query that uses a timestamp in the table to pull out the data exactly aligned to date boundaries.

• Luffydude: It doesnt need to be a specific exact time. Any kind of daily snapshot is better than no snapshot. I thought this would have a simple solution but that seems more hassle than what's worth :(
• jjanes: OK. I thought you wanted each tuple to be counted only to exactly one day's summary. If your snapshots are cumulative, then you are correct such precision isn't needed.

Answer 2

I'm afraid you need something like pgAgent or pg_cron extension