Postgres Pro
Downloads
Home   >   mailing lists

Re: How to rename the same column name in dozens of tables in Postgres? - Mailing list pgsql-general

From Raghavendra
Subject Re: How to rename the same column name in dozens of tables in Postgres?
Date
Msg-id CA+h6AhhUoFu2z7ZirbDpuCE-X_FKE57m59GWB0zBFdqPLqRJCQ@mail.gmail.com
Whole thread Raw
In response to How to rename the same column name in dozens of tables in Postgres?  (Stefan Schwarzer <stefan.schwarzer@unep.org>)
Responses Re: How to rename the same column name in dozens of tables in Postgres?  (Raghavendra <raghavendra.rao@enterprisedb.com>)
List pgsql-general
Tree view
On Tue, Dec 17, 2013 at 1:29 PM, Stefan Schwarzer <stefan.schwarzer@unep.org> 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. 

---
Regards,
Raghavendra
EnterpriseDB Corporation

pgsql-general by date:

Previous
From: Rahila Syed
Date:
Subject: Re: Replication: GZIP compression in WAL sender/receiver processes communication?
Next
From: Raghavendra
Date:
Subject: Re: How to rename the same column name in dozens of tables in Postgres?