Help with Update SQL

Asked

Viewed 36 times

-1

I need to learn how to update my chart. I have the "Person" table, this one has the "Description" column. At the time it was created a maxlenght of 100 characters was added, I need to change this so that 1000 characters can be added.

The detail is that I already have 25 clients, IE, were made 25 database, each one has Person>Description. Is there any way I can update all databases by SQL or only once? If yes, how can I do this?

Detail: I’m using the Postgresql

sql postgresql

  • You need the SQL command to change the column in the table or you want a script to run the command on all 25 database at once?

  • I want to know how to run SQL all at once...

  • From what I understand you want a script to run in 25 instances of "equal" database. You can create a precedent and make a loop to go through all its instances and run the alter table for each one. An SQL that runs in 2 or more instances is not possible.

  • Got it, never used scripts, you could give me an example of how I can do this?

1 answer

1

postgres keeps each database isolated, so if it is connected to the database x you cannot perform queries in the database y.

Of course there are resources for communication between different Databases, such as the postgres_fdw, for example, but in your case could be used the psql through the shell script.

SENHA_POSTGRES='senha'
USUARIO_POSTGRES='usuario'
LISTA_NOMES_BANCOS='db1 db2 db3'
COMANDO="alter table pessoa alter column descricao type varchar(1000);"

for NOME_BANCO in $LISTA_NOMES_BANCOS ; do
    echo 'Executando consultas no banco '  $NOME_BANCO
    PGPASSWORD=$SENHA_POSTGRES psql -h localhost -d $NOME_BANCO -U $USUARIO_POSTGRES -c "$COMANDO"
done

In the variable LISTA_NOMES_BANCOS the names of the databases to be modified will be defined. In for the SQL command will be executed by psql dynamically for each bank.

Browser other questions tagged sql postgresql

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