pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedRepear operations on 50 tables of the same schema?
2+ messages / 2 participants
[nested] [flat]
* Repear operations on 50 tables of the same schema?
@ 2023-02-27 11:53 celati Laurent <laurent.celati@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: celati Laurent @ 2023-02-27 11:53 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Repear operations on 50 tables of the same schema?
@ 2023-02-27 14:31 Ron <ronljohnsonjr@gmail.com>
parent: celati Laurent <laurent.celati@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Ron @ 2023-02-27 14:31 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-02-27 14:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-27 11:53 Repear operations on 50 tables of the same schema? celati Laurent <laurent.celati@gmail.com>
2023-02-27 14:31 ` Ron <ronljohnsonjr@gmail.com>
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