Altering data types of all columns in a table (PostgreSQL 13)

Question

I have a table import.hugo (import is schema) and I need to change all columns data type from text to numeric. The table already has data, so to alter column (for example) y I use this:

ALTER TABLE import.hugo ALTER COLUMN y TYPE numeric USING (y::numeric);

But the table has many columns and I don´t want to do it manually. I found something here and try it like this:

do $$
declare
t record;
begin
    for t IN select column_name, table_name
            from information_schema.columns
            where table_name='hugo' AND data_type='text'         
    loop
        execute 'alter table ' || t.table_name || ' alter column ' || t.column_name || ' type numeric';
    end loop;
end$$;

When I run it, it doesn't work, it says: relation "hugo" does not exist I tried many many more variants of this code, but I can't make it work. I also don't know, how to implement this part: USING (y::numeric); (from the very first command) into this command.

Ideally, I need it in a function, where the user can define the name of a table, in which we are changing the columns data type. So function to call like this SELECT function_name(table_name_to_alter_columns);

Thanks.

score 0 · Answer 1 · answered Mar 18 '21 at 18:10

Selecting table_name from information_schema.columns isn't enough, since the import schema is not in your search path. You can add the import schema to your search_path by appending import to whatever shows up in SHOW search_path, or you can just add the table_schema column to your DO block:

do $$
declare
t record;
begin
    for t IN select column_name, table_schema || '.' || table_name as full_name
            from information_schema.columns
            where table_name='hugo' AND data_type='text'         
    loop
        execute 'alter table ' || t.full || ' alter column ' || t.column_name || ' type numeric USING (' || t.column_name || '::numeric)';
    end loop;
end$$;

You need to add `and table_schema = 'import'` to the where condition. As it currently exists the query will return any table named *hugo* in all schemata, even if you do not have access to the schema. What happens when another schema has such a table and 1: you do have authority to do this but doing so is not wanted? 2: you do not have the necessary authority? Also are you sure all such text columns contain only numeric data. — Belayer, Mar 21 '21 at 03:41

Altering data types of all columns in a table (PostgreSQL 13)

1 Answers1