Re: Automate to rename table

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(dot)dba09(at)gmail(dot)com>:
>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(dot)oliver(at)gmail(dot)com> wrote:
>
>> Wild. What's the use-case here?
>>
>> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <
>> Matthew(dot)Wetmore(at)express-scripts(dot)com> 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(dot)dba09(at)gmail(dot)com>
>>> *Sent:* Wednesday, August 9, 2023 1:39 PM
>>> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org
>>> *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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>>