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 1qU7G1-00BiEj-J5 for pgsql-admin@arkaria.postgresql.org; Thu, 10 Aug 2023 15:07:25 +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 1qU7Fz-00G6WP-QY for pgsql-admin@arkaria.postgresql.org; Thu, 10 Aug 2023 15:07:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qU7Fz-00G6WG-Ew for pgsql-admin@lists.postgresql.org; Thu, 10 Aug 2023 15:07:23 +0000 Received: from mail-yw1-x112b.google.com ([2607:f8b0:4864:20::112b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qU7Fx-001iCT-LI for pgsql-admin@lists.postgresql.org; Thu, 10 Aug 2023 15:07:23 +0000 Received: by mail-yw1-x112b.google.com with SMTP id 00721157ae682-58419517920so12341637b3.0 for ; Thu, 10 Aug 2023 08:07:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691680040; x=1692284840; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=/CaL8CJ644vSpHlHx4CTOS0cGV1Xvc+eK/UiY1/caiM=; b=WzBvfUfk4uWcURv9CX9GnkF1bIhiemCMLiUlIryh4pxag+t9OyeNNSlLzDvKTWhfNm AlFEPvbvPJkn2bXHyW1QryrlGztV1olKCskkrz8uxSHBphkWHAtfaluMPr+PtoFFL6Fc Gfmvntwl6AmvK/bukPIy92LLynQ3clt1m55Z98PyjeZvvSAZgMgJnvR2nxUJBUWFlxhy 8YanleDIXE2Xkb+Vh0711OG895JQxMQfpd6Dce6ysgqTemThT+9pIjmSBYInFJK8r/yc vWStayn5CoDSLawueHvOvi/1eEbHnl9aUewinJW7tfvfekKxIdF/0Ez2SvPwTktR+JFr 6GHA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691680040; x=1692284840; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=/CaL8CJ644vSpHlHx4CTOS0cGV1Xvc+eK/UiY1/caiM=; b=dppGNcoFfIhgaUw/FerM2M+14lUXlpHqmvUGIUM0S8o+JTeiC4QSRLSdcRGblQQQQf OfSSVzTf9yvtCvXmc8WJsyclBxtEZ+Gb7Ty/Af4mXagY+0RQCs+GudhmLQTaUZu+uHNF 07j4gnzCe9jsaCTMBuEsBAXWVlE4IuoQ9GAEIH2PA8jwarhByiumg3bS8vPGd93DxZpR H/tzjzNue4dGhuNWrgakNCOSAm+FhKagplXMsgsV59uF2ob5DV5v95/XIHTkL1NjaxpY ePibA04/eVkVNOrUxFCJ6+Kfn7zI2b8wt1YcVrJcu4a0mev/AZHRbvCF0EdtHYaB8Abd nT/g== X-Gm-Message-State: AOJu0Yy6z58CEosGlGHEPCmNJScvzfSK+OImdyyUznmmfaTohHKKpgIs PiCsRn89bYC7NIO0MrRE0+/d4M7KC78= X-Google-Smtp-Source: AGHT+IHJQP/ezvDnPgYoP7buRZ56go5V+ajm7F4soUlVaXfS6NBINSoI+rqjYLcTLjmQtPdKgPxv0w== X-Received: by 2002:a81:a193:0:b0:579:dfd8:d4b3 with SMTP id y141-20020a81a193000000b00579dfd8d4b3mr2860682ywg.17.1691680039817; Thu, 10 Aug 2023 08:07:19 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:6075:3f19:4ef7:e118? ([2600:1700:bd2c:b810:6075:3f19:4ef7:e118]) by smtp.googlemail.com with ESMTPSA id d123-20020a814f81000000b00583a8754bbcsm387529ywb.108.2023.08.10.08.07.19 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 10 Aug 2023 08:07:19 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------4xNnjU2U0dbvboc2eMt4OQ0h" Message-ID: <13a922bb-7a21-d315-53b6-2c529eec63e7@gmail.com> Date: Thu, 10 Aug 2023 10:07:19 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Automate to rename table Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------4xNnjU2U0dbvboc2eMt4OQ0h Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Old school trigger+function based partitioning is exactly what you want. On 8/10/23 04:52, Rajesh Kumar wrote: > 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, wrote: > > Wild. What's the use-case here? > > On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) > 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 > *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 > -- Born in Arizona, moved to Babylonia. --------------4xNnjU2U0dbvboc2eMt4OQ0h Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Old school trigger+function based partitioning is exactly what you want.

On 8/10/23 04:52, Rajesh Kumar wrote:
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?

 



--

--
Born in Arizona, moved to Babylonia.
--------------4xNnjU2U0dbvboc2eMt4OQ0h--