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

Question

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.

Answer 1

You can use a DO statement to run a single dynamic command:

DO
$do$
BEGIN

RAISE NOTICE '%', (
-- EXECUTE (
   SELECT string_agg(format('ALTER TABLE %s ALTER COLUMN %I TYPE text'
                           , a.attrelid::regclass, a.attname), E';\n')
   FROM   pg_attribute a
   JOIN   pg_class     c ON c.oid = a.attrelid
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE  a.attname = 'description'
   AND    a.atttypid = 'varchar'::regtype
   AND    NOT a.attisdropped                      -- no dropped columns
   AND    a.attnum > 0                            -- no system columns (redundant check)
   AND    format_type(a.atttypid, a.atttypmod) = 'character varying(255)'
   AND    n.nspname NOT LIKE ALL ('{pg_%, information_schema}'::text[])
   );

END
$do$;

Since the command is potentially hazardous I commented the EXECUTE and put a RAISE NOTICE there instead. After confirming the commands are sane, switch the comment characters -- to actually execute the DDL commands.

The manual on format_type().

This changes the type for all columns description varchar(255), except for those in system catalogs, temporary tables (both starting with 'pg_') and the information schema. I build command from the system catalogs. @a_horse demonstrates the other good option to use the information schema instead.

In your particular case, there can only be one column per table. If there can be multiple, it would be substantially cheaper to execute all type changes in a single ALTER TABLE statement per table. Commands of the form:

ALTER TABLE foo
    ALTER COLUMN col1 TYPE text
  , ALTER COLUMN col2 TYPE text
  , ALTER COLUMN col3 TYPE text;

More details in this related answer:
Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Answer 2

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

Answer 3

You need to use the ALTER TABLE / ALTER COLUMN feature. A sample:

ALTER TABLE table_1
    ALTER COLUMN column_5 TYPE text,
    ALTER COLUMN column_10 TYPE text;

There is no global command to do this for you, but you could create a script to create all the ALTER TABLE / ALTER COLUMN commands and then run the script on your server.

Having said that, why do you want to change all of the 'description' columns to unlimited size? If a description should only be 100 characters (for example) it would be better to stick to a definition VARCHAR(100) that identifies what the limit should be.