Question 89591 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql : Repeat operations on 50 tables of the same schema?

I am new to Postgresql (v.13). 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.

Can someone could help me?

Tags:

2

Answers


  1. 0

    For bulk operations like that, psql‘s gexec is invaluable. It allows you to write an SQL statement that generates SQL statements and then execute each result row as a statement. For the rename, that could look like this:

    SELECT format(
          'ALTER TABLE ign.%I RENAME TO %i',
          table_name,
          'IGN_bdTopo_' || table_name || 'V1'
       )
FROM information_schema.tables
WHERE table_schema = 'ign'
  AND table_type = 'BASE TABLE' gexec

    The metadata views in information_schema are very useful for this, and the format() function makes it easy to avoid SQL injection problems.

    I recommend not to use upper case characters in object names.

    Login or Signup to reply.
  2. 0

    Your code structure will be as below. You can add other operations which you need.

    DO $$

DECLARE
table_names text;
schema_names text='public';


BEGIN

FOR table_names IN 
select table_name  FROM information_schema.tables
WHERE table_schema = schema_names
LOOP
RAISE NOTICE 'added columns to %', table_names;

EXECUTE 'alter table "'||schema_names||'"."' || table_names || '" add  column if not exists  date date ';
EXECUTE 'alter table "'||schema_names||'"."' || table_names || '" add column if not exists source character varying(50)';


END LOOP;

END;

$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search