6

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.

1
  • All tables in all schemas except system catalogs? Or all tables in a specific schema? – Erwin Brandstetter Jun 2 '14 at 16:57
5

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

8

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
1

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.

1
  • 1
    @dezso Got it. I can see that as a rule of thumb varchar(255) is probably varchar(max). But having the intent of the data (e.g. one line on a report) control the data size is another useful rule of thumb. – RLF Jun 2 '14 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.