Обсуждение: How to rename the same column name in dozens of tables in Postgres?
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
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.
---
Regards,
Raghavendra
EnterpriseDB Corporation
On Tue, Dec 17, 2013 at 2:23 PM, Raghavendra <raghavendra.rao@enterprisedb.com> 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!
StefanYou 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/bashOLDCOLUMN=aaNEWCOLUMN=afor 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;"doneReplace 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
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!
StefanYou 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/bashOLDCOLUMN=aaNEWCOLUMN=afor 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;"doneReplace 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$OLDCOLUMNto$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