Changing all character varying to text across all tables and columns

Question

I'm trying to find a way to iterate over all of my tables and changing each charvar[n] to text and charvar[][n] to text[]. However, only found ways to change it for a specific table and column or specific column name. Using postgres 10. Any advice?

Thanks

Answer 1

For one-off changes, create a query to generate the SQL commands so that you can review them:

SELECT format('ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE text;',
              table_schema, table_name, column_name)
FROM information_schema.columns
WHERE udt_name = 'varchar'           -- '_varchar' for arrays
  AND table_schema = 'my_schema';