Rename all columns from all tables with specific column name in PostgreSQL?

Question

Is there a way I can rename all column names that are called 'location' to 'location_name' within all schemas in my PostgreSQL database?

I am fairly new to SQL and am aware there is an ALTER TABLE command but don't know if it is possible to somehow loop through all tables?

Answer 1

You need dynamic SQL for this using an anonymous PL/pgSQL block to do this in an automated way:

do
$$
declare
  l_rec record;
begin
  for l_rec in (select table_schema, table_name, column_name 
                from information_schema.columns 
                where table_schema = 'public' 
                  and column_name = 'location') loop
     execute format ('alter table %I.%I rename column %I to %I_name', l_rec.table_schema, l_rec.table_name, l_rec.column_name, l_rec.column_name);
  end loop;
end;
$$

If you have multiple schemas or your tables are not in the public schema, replace the condition where table_schema = 'public' with the appropriate restriction.