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 1qU7Et-00BiAF-Pc for pgsql-admin@arkaria.postgresql.org; Thu, 10 Aug 2023 15:06:16 +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 1qU7Es-00G0eo-Az for pgsql-admin@arkaria.postgresql.org; Thu, 10 Aug 2023 15:06:14 +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 1qU7Er-00G0ee-VZ for pgsql-admin@lists.postgresql.org; Thu, 10 Aug 2023 15:06:14 +0000 Received: from mail-yw1-x1134.google.com ([2607:f8b0:4864:20::1134]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qU7Ep-001iAv-Nv for pgsql-admin@lists.postgresql.org; Thu, 10 Aug 2023 15:06:13 +0000 Received: by mail-yw1-x1134.google.com with SMTP id 00721157ae682-583b0637c04so14518547b3.1 for ; Thu, 10 Aug 2023 08:06:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691679969; x=1692284769; 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=yJbvjGU0jLInovxpDth0msXp1E354EIECVqGgliYMzk=; b=dL/hhk4tzIcxuysKghdE9BNEC4xOvQP+RxKJHeM9EG2aRiqSb/ueIsxtby8OAQuBsO ahIe+lRoU7H7VuVPSKeinp0mNDYywfvCBOyg51PC1TgOFCfQELKXUd3+/l1uGIcp46Ua s5S2Nhf3yHcYTTg6d5wtnkabk93g6kP/pDo0Yefi3OL83FNZT2C395dZU6nt/b+Qkzy0 xoJ6b1ddi3oqiR4hpFYtAyQ7UjzPzfDgsxRBDDIjuIWX9YUZPEc13+UetbzHDxekD8iI b1aDza5Jfw6X67r3+8pS0J79sKWqoJaS/G8EKsiMp3TeMEvkDs6Vfu+knXVb47Bbd3TW brNg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691679969; x=1692284769; 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=yJbvjGU0jLInovxpDth0msXp1E354EIECVqGgliYMzk=; b=DCE8vcMzVN5HbUdXvTRLokCQGiqwLBsdc3sN4d1UpjQp2bjSZkEF77prE5Q7bl8lzE rak8D4cK/rU3DkhdGI2gIypdzcj3YtGQkH1he0YEX/RDiPlKzyf1Go0nXdbviEXRVPQb NpQEzguBFcKjHtQ/+ugQ53CQJPvNCDuIaKIKImftgmWL5z2cZ5KekkPWnROhfTEQFK6y CZgxmmxsN/Jh/HkQFvXpg3WgiEvrqo+eHAkFhspMW0Pa5r1GOo0NxhI7HUN7WSQNMolU jZ2O+EsSz7/a48njcnrfR9AuS5HULxv0sXuAh9P4c7jcnsfADQrRgrH4W6R4zE01hnlG l4BQ== X-Gm-Message-State: AOJu0YyVI+nQT8zdz3ahLNPHoLdnh/JvR4iF4P6q/Qb7RbrGPrn4s8cf DnhkX6P3jsOYl1Q34sqTXm20T8DEfvE= X-Google-Smtp-Source: AGHT+IFV5PHZAZyF/rYmAUahF43pYurmkW2HleN4TKpNTUGithtmFqlfG2fyTgjU+dJSMNpJTuy4wg== X-Received: by 2002:a0d:d68d:0:b0:577:41e2:5011 with SMTP id y135-20020a0dd68d000000b0057741e25011mr2263345ywd.26.1691679969333; Thu, 10 Aug 2023 08:06:09 -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 i65-20020a0df844000000b005773afca47bsm395073ywf.27.2023.08.10.08.06.08 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 10 Aug 2023 08:06:08 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------Z6TYSLqQW4aucfJ0t599WRey" Message-ID: <74f7caf8-c1f6-d47a-cb79-a9ac8dcacaf4@gmail.com> Date: Thu, 10 Aug 2023 10:06:08 -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: <14844CC2-42CB-4A2B-A656-59F6DD20CE1C@jakobs.com> From: Ron In-Reply-To: <14844CC2-42CB-4A2B-A656-59F6DD20CE1C@jakobs.com> 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. --------------Z6TYSLqQW4aucfJ0t599WRey Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > : > > 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. --------------Z6TYSLqQW4aucfJ0t599WRey Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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?

 



--

--
Born in Arizona, moved to Babylonia.
--------------Z6TYSLqQW4aucfJ0t599WRey--