Changing all character varying to text across all tables and columns
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';