CODEWITHSUNDEEP
databasepostgresqlshell
skyrocker
skyrocker

Reputation: 199

Update each row of PostgreSQL table using shell script

I have a shell script which has a list of ips.

Under the for loop of that script, every time 15 ips are picked and changed the status to 'removed'.

Can some expert shed some light on how should I update those list of ips under a shell script's for loop? Meaning what should I do from inside the for loop of shell script to make those continuous changes to PostgreSQL database?

Upvotes: 1

Views: 3025

Answers (2)

Waqar Ahmed
Waqar Ahmed

Reputation: 1

psql -U pgsql -d database -c "statement"

For FreeBSD based systems at least, the above statement would work. The flags are different in postgres 10. Likely the same would be true for linux as well.

Upvotes: 0

Nidhoegger
Nidhoegger

Reputation: 5232

You can make changes to a PostgreSQL database using the command line tool psql. If you have a password needed for the updated you can set the PGPASSWORD environment variable. Example:

PGPASSWORD="mypassword" psql -U username -c database "statement"

If no password is needed, do

psql -U username -c database "statement"

and if no username is needed for the access, you can do

psql -c database "statement"

For the random IPs to change, I would either create an array of all IPs using psql with a

SELECT * FROM ips;

and then create 15 random numbers, use these as index (of course you have to make sure no random number is created twice) and then set them to status removed in the database.

After reading the comments: To update you could do:

updateIP=`echo ip_string | awk -F' ' '{print $1}'`
psql -U user -c database "UPDATE iptable SET status='status_removed' WHERE ip='${updateIP}';"

To update a single record, given you store the IP in the table "iptable" enter the column "ip" and the status under column "status".

Upvotes: 3

Related Questions