I'm writing a bash script where I use psql (Postgresql).

DATABASE='scifi'
sudo -u postgres psql
count=SELECT COUNT(*) FROM (SELECT datname FROM pg_catalog.pg_database WHERE lower(datname)=lower($DATABASE)

I want to delete the database if exist and recreated with the same name and allocated to an user.

Also other operations related to psql.

My issue is that once I enter psql I cannot pass bash commands and retrieve info. I kind of understand why(because I'm moving from bash to psql) but I have to find a solution.

1 Answer

You can pass single commands as strings using -c, or pass more complex command sequences via standard input for example using a here document:

sudo -u postgres psql << EOF
SELECT COUNT(*) FROM ( SELECT datname FROM pg_catalog.pg_database WHERE lower(datname)=lower($DATABASE)
)
EOF

You may need to quote or escape shell-special characters such as * that you do not want to be expanded.

See PostgreSQL 9.4.19 Documentation

---

To capture the result, use the shell's $(...) command substitution syntax:

count=$(sudo -u postgres psql << EOF
SELECT COUNT(*) FROM ( SELECT datname FROM pg_catalog.pg_database WHERE lower(datname)=lower($DATABASE)
)
EOF
)

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy