create postgresql trigger or function to delete old data from one table and insert into alias table

0
votes

In PostgreSQL i want to create function and trigger which can delete old data of before 8 day from A table(which contain many rows) and insert this data into one new alias table. and i want to execute this trigger everyday.

1
sqlpostgresqlpostgresql-9.1database-trigger
try begin; insert into at select ..where ts < now() - '8 days'::interval; delete from t where ts < now() - '8 days'::interval; end; - Vao Tsun
i want to delete data from table table A and insert same deleted data into another table B. - pramod
of before 8 days - pramod
can you please tell this in details - pramod
yes I could. Can you please tell what you need in details? - Vao Tsun

1 Answers

2
votes

historical table creation:

create table b as select * from a where false;

moving data

begin; 
  insert into b select * from a where ts < (now() - '8 days'::interval); 
  delete from a where ts < (now() - '8 days'::interval); 
end;

scheduling

  1. create a file a.sql and put the above transaction to it
  2. add to cron psql -f a.sql

notes creating trigger that will delete data on each statement is a bad idea