Postgresql - Change multiple column names at once

Asked

Viewed 62 times

0

I have a table in Postgresql with columns that are named "c0001", "c0992", "c3993", and so on. They start with c and then comes a "random number".

I have a dictionary in . csv explaining each of them and with a nickname. Ex:

    Conta  |        Descricao            | Apelido
    c0001  | Valor disponível em caixa   |  Caixa
    c0992  | Valor do Patrimônio Líquido |  PL
     ...   |            ....             |  ...

To rename the column, use

ALTER TABLE schema.balanco RENAME c0001 TO Caixa

I need to do this for the 204 columns. Is there any way to do this without having to type one by one?

postgresql
  • 2

    go to Notepad++, generate a macro, assemble the full script...

  • 1

    You can open the file with any text editor that allows you to replace words with regular expression and have them replaced \s+\|.*\|\s+ for TO and then have them replace (spaces at the beginning of each line) by ALTER TABLE schema.balanco RENAME .

1 answer

0


I followed the tips of friends and decided as follows:

imported the file in R and used the commands:

library(stringr)

Plano_Contas$CONTA <- str_c("COMMENT ON COLUMN schema.balanco.c",Plano_Contas$CONTA)

Plano_Contas$NOME_CONTA <- str_c(" IS '",Plano_Contas$NOME_CONTA,"';")

write.csv(Plano_Contas,"planocontas.csv")

I opened . csv in Excel, copied the two columns and pasted in Postgresql

Worked.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.