Creating a partitioned table with a partition by month would be the way to go. Then drop the tables you no longer need. This can be done in a cron job or by using pg_agent, pg_cron or pg_timetable.
Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx>:
I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.How do we automate it?I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days?What's the best way?On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@xxxxxxxxx> wrote:Wild. What's the use-case here?On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@xxxxxxxxxxxxxxxxxxx> wrote:Simple CRON entry with a script that runs a psql command
You can:
alter table rename
Or
create table x as select * from original table.
Depending on you DDL needs or permissions/setup
From: Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Automate to rename table
Hi friends,
What is the best way to rename the table name automatically every 30 minutes?
--Wells Oliver
wells.oliver@xxxxxxxxx
