Re: Automate to rename table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


That requires adding the date field to the PK.

On 8/10/23 05:11, Holger Jakobs wrote:
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


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?



Born in Arizona, moved to Babylonia.

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux