Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qU2dy-00BSTi-UR for pgsql-admin@arkaria.postgresql.org; Thu, 10 Aug 2023 10:11:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qU2dx-00DUvU-A6 for pgsql-admin@arkaria.postgresql.org; Thu, 10 Aug 2023 10:11:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qU2dw-00DUvM-Qn for pgsql-admin@lists.postgresql.org; Thu, 10 Aug 2023 10:11:49 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qU2du-001ZEV-CX for pgsql-admin@lists.postgresql.org; Thu, 10 Aug 2023 10:11:47 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 8AEEB38045B for ; Thu, 10 Aug 2023 12:11:43 +0200 (CEST) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id T5orJ5zbb1my for ; Thu, 10 Aug 2023 12:11:43 +0200 (CEST) Received: from [127.0.0.1] (dynamic-046-114-155-038.46.114.pool.telefonica.de [46.114.155.38]) by rs.plausibolo.de (Postfix) with ESMTPSA id 13E4D3803CD for ; Thu, 10 Aug 2023 12:11:43 +0200 (CEST) Date: Thu, 10 Aug 2023 12:11:41 +0200 From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Subject: Re: Automate to rename table User-Agent: K-9 Mail for Android In-Reply-To: References: Message-ID: <14844CC2-42CB-4A2B-A656-59F6DD20CE1C@jakobs.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=----FQQRL7H2FYYF58ADDY5JOG9B87794K Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------FQQRL7H2FYYF58ADDY5JOG9B87794K Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Creating a partitioned table with a partition by month would be the way to = go=2E Then drop the tables you no longer need=2E This can be done in a cron= job or by using pg_agent, pg_cron or pg_timetable=2E Am 10=2E August 2023 11:52:37 MESZ schrieb Rajesh Kumar : >I have a table A where datas are coming in=2E=2E=2Eand as it gets old (sa= y 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=2E > > >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=2E And wh= en >we execute that function it create a new table A so that new data gets >inserted=2E Then after 60 days we drop drop A_old table=2E Which means we= need >30 days data in new table, 30 days data in old table and remaining we don= 't >need it=2E > >How do we automate it? > >I am thinking of partioning, but unsure whether it's a complex process >=2E=2Ealso don't have date column=2E > >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, = wrote: > >> Wild=2E What's the use-case here? >> >> On Wed, Aug 9, 2023 at 9:50=E2=80=AFPM Wetmore, Matthew (CTR) < >> Matthew=2EWetmore@express-scripts=2Ecom> 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=2E >>> >>> >>> >>> Depending on you DDL needs or permissions/setup >>> >>> >>> >>> *From:* Rajesh Kumar >>> *Sent:* Wednesday, August 9, 2023 1:39 PM >>> *To:* pgsql-admin@lists=2Epostgresql=2Eorg >>> *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=2Eoliver@gmail=2Ecom >> ------FQQRL7H2FYYF58ADDY5JOG9B87794K Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Creating a partitioned table wit= h a partition by month would be the way to go=2E Then drop the tables you n= o longer need=2E This can be done in a cron job or by using pg_agent, pg_cr= on or pg_timetable=2E


Am 10=2E August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkuma= r=2Edba09@gmail=2Ecom>:
I have a table A where datas are coming in=2E=2E=2Eand a= s 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 ta= ble=2E


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=2E An= d when we execute that function it create a new table A so that new data ge= ts inserted=2E Then after 60 days we drop drop A_old table=2E Which means w= e need 30 days data in new table, 30 days data in old table and remaining w= e don't need it=2E

How d= o we automate it?

I am t= hinking of partioning, but unsure whether it's a complex process =2E=2Ealso= don't have date column=2E

Can we simply create a function to alter table A rename to A_old , and s= chedule to execute this function every 30 days? 

What's the best way?

On Thu, 10 Aug, 202= 3, 2:34 PM Wells Oliver, <= wells=2Eoliver@gmail=2Ecom> wrote:
Wild=2E What's the use-case here?

On Wed, Aug 9, 2= 023 at 9:50=E2=80=AFPM Wetmore, Matthew (CTR) <Matt= hew=2EWetmore@express-scripts=2Ecom> 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 ta= ble=2E

 

Depending on you DDL needs or permissions/s= etup

 

From: Rajesh Kumar <rajeshkumar=2Edba09@gmail= =2Ecom>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists=2Epostgresql=2Eorg
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name autom= atically every 30 minutes?

 



--
------FQQRL7H2FYYF58ADDY5JOG9B87794K--