I have a table X and another table Y with same schema. When a record is inserted in X, after 24 hours, it should be moved to Y automatically. How can I achieve this in postgres?
Postgres does not have such automation, you have to use external tools. Eg, make a cronjob to run the script which will do that, smth like:
pslq -d dbname -c "begin; with d as (delete from x where ts < now() - '1 day'::interval returning *) insert into yselect * from d;; end;"