Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text.

If anyone knows that I would be very glad for your help.

I guess you don't want to manually run all the necessary ALTER TABLE statements.

You can use the following statement to generate the needed ALTER TABLE statements:

select 'alter table '||table_schema||'.'||table_name||' alter column '||column_name||' type text;'
from information_schema.columns
where table_schema = 'public'
  and column_name = 'description'
  and data_type = 'character varying'
  and character_maximum_length = 255;

replace 'public' with your schema if your tables are not located in the public schema.

Run the above statement, spool the output into a text file, then run the generated script.

Something like this:

psql (9.3.4)
Type "help" for help.
postgres=> \t
Showing only tuples.
postgres=> \o alter.sql
postgres=> select 'alter table '||table_schema||'.'||table_name||' alter column '||column_name||' type text;'
postgres-> from information_schema.columns
postgres-> where table_schema = 'public'
postgres-> and column_name = 'description'
postgres-> and data_type = 'character varying'
postgres-> and character_maximum_length = 255;
postgres=> \i alter.sql

StackExchange Database Administrators Q#66387, answer score: 8