Postgres pgAdmin multiple column renames at once

Question

Is there a better way to rename a column from footballer_id to id. (Removing the footballer_ from all the columns of the table)

ALTER TABLE public."Footballer" RENAME COLUMN "footballer_id" TO "id"
ALTER TABLE public."Footballer" RENAME COLUMN "footballer_team" TO "team"

Answer 1

You can write a query to fetch the column name and then to format and run a command using the results. It makes use of psql \gexec parameter.

SELECT format('ALTER TABLE footballer RENAME COLUMN %I to %I', column_name, substring(column_name,length('footballer_')+1))
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'footballer'
  AND column_name like 'footballer_%';\gexec