> 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
Thanks so much. I had to add the database user and database name: psql -U XXX -c "select 'alter table '||table_schema||'.'||table_name||' rename column $OLDCOLUMN to $NEWCOLUMN;' from information_schema.tables where table_schema='gis';" my_database | psql -U XXX my_database and still run in an error. But it renamed quite a bunch of the tables. So, very good! Stef
