pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Repear operations on 50 tables of the same schema?
Date: Mon, 27 Feb 2023 08:31:32 -0600
Message-ID: <cc7fd3d6-11c1-0583-9afe-e09555b112be@gmail.com> (raw)
In-Reply-To: <CAHByMH36q6OCCKZWJML4vT6cq1aADSi-wghue_7qOcG-tbLcDQ@mail.gmail.com>
References: <CAHByMH0Vu3wu-8cBS-mEdEqS_6p1Lid5G9Sh++mJSE=O9-_y1g@mail.gmail.com>
	<CAHByMH36q6OCCKZWJML4vT6cq1aADSi-wghue_7qOcG-tbLcDQ@mail.gmail.com>

On 2/27/23 05:53, celati Laurent wrote:
>
> Good morning,
> I am new to Postgresql. I have 50 tables into a "ign" schema (schema other 
> than public).
> I would like for each of these 50 tables:
>
> - Add a prefix to the name of the table: "IGN_bdTopo_"
> - Add a suffix to the table name: "_V1"
> - create a new "date" column of date type. And populate this field with 
> the value: 06/15/2021
> - create a new "source" column of type varchar (length 50). And populate 
> this field with the value: 'ign'.
> - move all the elements of these 50 tables (including all). from the "ign" 
> schema to the "ign_v2" schema. Whether data, constraints, indexes.
>
> If someone could  help me? Thank you so much.

This is what I'd do, just to get it done.  It presumes you know bash 
scripting, and how to use psql.

https://www.postgresql.org/docs/13/sql-createschema.html
https://www.postgresql.org/docs/13/sql-altertable.html

First, CREATE SCHEMA ign_v2;

Write a bash script that uses psql queries information_schema.tables.  
There's be a for loop for all the tables.

Inside the loop, still using bash, and using psql:
1. create the new table name then execute "ALTER TABLE ... RENAME TO ...;",
2. ALTER TABLE (new_name) ADD COLUMN some_date DATE, ADD COLUMN some_source 
varchar(50);
3. UPDATE (new_name) SET some_date = '2021-06-15'::date, some_source = 'ign';
4. ALTER TABLE (new_name) SET SCHEMA ign_v2;


-- 
Born in Arizona, moved to Babylonia.

view thread (2+ messages)

Message-ID: <cc7fd3d6-11c1-0583-9afe-e09555b112be@gmail.com>
Permalink:  ../cc7fd3d6-11c1-0583-9afe-e09555b112be@gmail.com/
Also on:    postgresql.org/message-id/cc7fd3d6-11c1-0583-9afe-e09555b112be@gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Repear operations on 50 tables of the same schema?
  In-Reply-To: <cc7fd3d6-11c1-0583-9afe-e09555b112be@gmail.com>
  References: <CAHByMH0Vu3wu-8cBS-mEdEqS_6p1Lid5G9Sh++mJSE=O9-_y1g@mail.gmail.com> <CAHByMH36q6OCCKZWJML4vT6cq1aADSi-wghue_7qOcG-tbLcDQ@mail.gmail.com> <cc7fd3d6-11c1-0583-9afe-e09555b112be@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