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

On Tue, Dec 17, 2013 at 2:23 PM, Raghavendra <
raghavendra(dot)rao(at)enterprisedb(dot)com> wrote:

> On Tue, Dec 17, 2013 at 1:29 PM, Stefan Schwarzer <
> stefan(dot)schwarzer(at)unep(dot)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.
>
> 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