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

Postgresql – Need to Alter data type in a column common to all tables in a schema

I need to alter the Data Type of a named column (let’s call them ‘photo’) across all the tables in a schema.

For example: I have 300 tables with identical structure (except the data in each row of course).
But I have to change the varchar(255) in the photo column to (500) on every table.
Is there a way to do it without going through every table and doing it manually?

I tried everything I found on the internet. Used ChatGPT (useless as always) but to no avail.

Tags:

2

Answers


  1. 0

    An example, obviously not tested on your instance, so try out on test database first. In psql do:

    BEGIN;

SELECT format('ALTER TABLE %s ALTER COLUMN photo TYPE varchar ;', pg.relname)
FROM
(
 SELECT
    relname
 FROM
    pg_class
 WHERE
    relnamespace = 'public'::regnamespace
 AND
    relkind = 'r'
) pg
gexec

--COMMIT; or ROLLBACK; depending on outcome

    Change the relnamespace value to the schema you are looking to change the tables in.

    Login or Signup to reply.
  2. 0

    This query on information_schema shows both the list of all tables with the column to be altered and in the last column the ALTER statement to perform the change.

    Adjust the schema/column name as required.

    SELECT table_schema, table_name, column_name, data_type, character_maximum_length,
format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE text;', table_schema, table_name, column_name) sql_txt
  FROM information_schema.columns
 WHERE table_schema = 'jdbn'
   AND column_name  = 'photo'
order by table_name;

    Sample output

    table_schema|table_name                          |column_name|data_type        |character_maximum_length|
------------+------------------------------------+-----------+-----------------+------------------------+
jdbn        |Table2                              |photo      |character varying|                     200|
jdbn        |harmless; but here is something evil|photo      |character varying|                     300|
jdbn        |t1                                  |photo      |character varying|                     100|

    Note that to format the ALTER statement the I type is used, to avoid problems with mixed case names and SQL injection as commented.

    I recommend for this ad hoc task a two step strategy:

    1. copy the content of the last column in a text file.

    ALTER TABLE jdbn."Table2" ALTER COLUMN photo TYPE text;

    ALTER TABLE jdbn."harmless; but here is something evil" ALTER COLUMN photo TYPE text;

    ALTER TABLE jdbn.t1 ALTER COLUMN photo TYPE text;

    1. run it as a SQL script. Recheck the result with the query above and commit if OK.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search