From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Automate to rename table
Date: Thu, 10 Aug 2023 10:06:08 -0500
Message-ID: <74f7caf8-c1f6-d47a-cb79-a9ac8dcacaf4@gmail.com> (raw)
In-Reply-To: <14844CC2-42CB-4A2B-A656-59F6DD20CE1C@jakobs.com>
References: <CAJk5AtaNKtSp2thxJd56j=he15aHHeUbW7tmnhYhY_1iiKG9OA@mail.gmail.com>
<a4aa1ded524e423ca3ebbd0fea723315@express-scripts.com>
<CAOC+FBWEV2FtkGLjZHsEjKHki5WL0VSiBJhymdkny1JHHfggPQ@mail.gmail.com>
<CAJk5AtYMsFAGz=aDOqXn5rMmMq=LyBQEsA2XVoCwFYTv6=3mYw@mail.gmail.com>
<14844CC2-42CB-4A2B-A656-59F6DD20CE1C@jakobs.com>
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@gmail.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.oliver@gmail.com>
> wrote:
>
> Wild. What's the use-case here?
>
> On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR)
> <Matthew.Wetmore@express-scripts.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.dba09@gmail.com>
> *Sent:* Wednesday, August 9, 2023 1:39 PM
> *To:* pgsql-admin@lists.postgresql.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.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Automate to rename table
In-Reply-To: <74f7caf8-c1f6-d47a-cb79-a9ac8dcacaf4@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox