![]() Run the above statement, spool the output into a text file, then run the generated script. Replace 'public' with your schema if your tables are not located in the public schema. 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 ' I guess you don't want to manually run all the necessary ALTER TABLE statements. Commands of the form: ALTER TABLE fooĭynamic UPDATE fails due to unwanted parenthesis around string in plpgsql If there can be multiple, it would be substantially cheaper to execute all type changes in a single ALTER TABLE statement per table. In your particular case, there can only be one column per table. demonstrates the other good option to use the information schema instead. I build command from the system catalogs. 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. After confirming the commands are sane, switch the comment characters - to actually execute the DDL commands. ![]() Since the command is potentially hazardous I commented the EXECUTE and put a RAISE NOTICE there instead. JOIN pg_namespace n ON n.oid = c.relnamespaceĪND NOT a.attisdropped - no dropped columnsĪND a.attnum > 0 - no system columns (redundant check)ĪND format_type(a.atttypid, a.atttypmod) = 'character varying(255)'ĪND n.nspname NOT LIKE ALL (''::text) , a.attrelid::regclass, a.attname), E' \n') SELECT string_agg(format('ALTER TABLE %s ALTER COLUMN %I TYPE text' ![]() You can use a DO statement to run a single dynamic command: DO ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |