Re: How to rename the same column name in dozens of tables in Postgres?

[Raghavendra <raghavendra.rao@xxxxxxxxxxxxxxxx>]

On Tue, Dec 17, 2013 at 2:23 PM, Raghavendra <raghavendra.rao@xxxxxxxxxxxxxxxx> wrote:

On Tue, Dec 17, 2013 at 1:29 PM, Stefan Schwarzer <stefan.schwarzer@xxxxxxxx> wrote:

Hi there,

I have dozens of tables where I need to rename one column in each of these. Is there any generic way to do that?

I am not really familiar with the scripting possibilities of Postgres.

Thanks a lot for your help!

Stefan

You can do with script. I made one on fly for this, other's might have more polished version. 

-bash-4.1$ more rename.sh

#!/bin/bash

OLDCOLUMN=aa

NEWCOLUMN=a

for i in $(psql -t -c "select table_schema||'.'||table_name from information_schema.tables where table_schema='public';")

do

  /opt/PostgreSQL/9.3/bin/psql -p 5432 -U postgres -c "alter table $i rename column $OLDCOLUMN to $NEWCOLUMN;"

done

Replace the port,user,OLDCOLUMN, NEWCOLUMN and SCHEMA according to your requirement. 

One more way from command line

-bash-4.1$ OLDCOLUMN=xyz

-bash-4.1$ NEWCOLUMN=abc

-bash-4.1$ psql -c "select 'alter table '||table_schema||'.'||table_name||' rename column

$OLDCOLUMN

 to

$NEWCOLUMN

;' from information_schema.tables where table_schema='public';" | psql

--Raghav