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?
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.
If you have superuser privileges you can make the changes in one sweep in the system catalogs:
UPDATE pg_attribute
SET attname = 'location_name'
WHERE attname = 'location';
I think the closest you will get is running something like this:
SELECT FORMAT(
'ALTER TABLE %I.%I.%I RENAME COLUMN %I TO %I;',
catalog_name,
schema_name,
table_name,
column_name,
'location_name'
)
FROM information_schema.columns
WHERE column_name = 'location';
If you run that in psql, you can follow it with \gexec and it should work.
You can fetch all location columns in your database with
SELECT * FROM information_schema.columns WHERE column_name = 'location';
And probably use some programming language to then execute all rename queries. There are information_schema tables in almost every database management system (although, they are sometimes called/structured differently).
I just had the same problem, and the following code worked fine for me (just replace 'foo' and 'bar' with your old and new column names):
DO $$
DECLARE
l_rec record;
BEGIN
FOR l_rec IN (SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name = 'foo')
LOOP
EXECUTE FORMAT('ALTER TABLE %I.%I RENAME COLUMN %I TO %I',
l_rec.table_schema, l_rec.table_name,
l_rec.column_name, 'bar');
END LOOP;
END;
$$;
