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

Question

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.

Answer 1

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.oid::regclass)
FROM
(
 SELECT
    oid
 FROM
    pg_class
 WHERE
    relnamespace = 'public'::regnamespace
 AND
    relkind = 'r'
) pg
\gexec

--COMMIT; or ROLLBACK; depending on outcome

UPDATE

Per Laurenz Albe's suggestion changed relname to oid in the sub-query and then pg.relname to pg.oid::regclass in the outer query as extra precaution against SQL injection.

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

Answer 2

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;

2. run it as a SQL script. Recheck the result with the query above and commit if OK.

Answer 3

try this

DO $$
DECLARE
table_name text;
BEGIN
FOR table_name IN (SELECT tablename FROM pg_tables WHERE schemaname = 
'your_schema_name') LOOP
    EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN photo TYPE 
varchar(500);';
END LOOP;
 END $$;