[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Repear operations on 50 tables of the same schema?
From:       Ron <ronljohnsonjr () gmail ! com>
Date:       2023-02-27 14:31:32
Message-ID: cc7fd3d6-11c1-0583-9afe-e09555b112be () gmail ! com
[Download RAW message or body]

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.
[Attachment #3 (text/html)]

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 2/27/23 05:53, celati Laurent wrote:<br>
    <blockquote type="cite"
cite="mid:CAHByMH36q6OCCKZWJML4vT6cq1aADSi-wghue_7qOcG-tbLcDQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="auto">
        <div class="gmail_quote" dir="auto">
          <div dir="ltr" class="gmail_attr"><br>
          </div>
          <div dir="ltr">Good morning,<br>
            I am new to Postgresql. I have 50 tables into a "ign" schema
            (schema other than public).<br>
            I would like for each of these 50 tables:<br>
            <br>
            - Add a prefix to the name of the table: "IGN_bdTopo_"<br>
            - Add a suffix to the table name: "_V1"<br>
            - create a new "date" column of date type. And populate this
            field with the value: 06/15/2021<br>
            - create a new "source" column of type varchar (length 50).
            And populate this field with the value: 'ign'.<br>
            - move all the elements of these 50 tables (including all).
            from the "ign" schema to the "ign_v2" schema. Whether data,
            constraints, indexes.<br>
            <br>
            If someone could  help me? Thank you so much.<br>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    This is what I'd do, just to get it done.  It presumes you know bash
    scripting, and how to use psql.<br>
    <br>
    <a class="moz-txt-link-freetext" \
href="https://www.postgresql.org/docs/13/sql-createschema.html">https://www.postgresql.org/docs/13/sql-createschema.html</a><br>
  <a class="moz-txt-link-freetext" \
href="https://www.postgresql.org/docs/13/sql-altertable.html">https://www.postgresql.org/docs/13/sql-altertable.html</a><br>
  <br>
    First, CREATE SCHEMA ign_v2;<br>
    <br>
    Write a bash script that uses psql queries
    information_schema.tables.  There's be a for loop for all the
    tables.<br>
    <br>
    Inside the loop, still using bash, and using psql:<br>
    1. create the new table name then execute "ALTER TABLE ... RENAME TO
    ...;",<br>
    2. ALTER TABLE (new_name) ADD COLUMN some_date DATE, ADD COLUMN
    some_source varchar(50);<br>
    3. UPDATE (new_name) SET some_date = '2021-06-15'::date, some_source
    = 'ign';<br>
    4. ALTER TABLE (new_name) SET SCHEMA ign_v2;<br>
    <br>
    <br>
    <div class="moz-signature">-- <br>
      Born in Arizona, moved to Babylonia.</div>
  </body>
</html>



[prev in list] [next in list] [prev in thread] [next in thread]

Configure | About | News | Add a list | Sponsored by KoreLogic