Postgresql – Fetching wrong results on comparing a variable with 0 in unix

postgresqlscriptingunix

I have the following code in unix

 #!/bin/bash
 user=$1
 username=$user'%'
 DB_name=$2
 userpwd=$4
 export PGPASSWORD=$3

 psql -h localhost -U postgres -d $DB_name -At -c "SELECT COUNT(*) AS USER_COUNT FROM pg_roles WHERE rolname = '${user}' " \
| while read -a Record ; 
do
USER_COUNT=${Record[0]} 
echo 'user USER_COUNT ' $USER_COUNT
done

echo 'count' $count
if   [[ $(($USER_COUNT)) -eq 0 ]]
then
echo 'New User ' $user
else
echo 'User Exists ' $user

fi

It basically accepts a given username and checks if it exists in Postgresql.
After we pass the arguments, it checks its count, which will be 1, if the username exists, otherwise 0, and stores it in the variable USER_COUNT.
It then compares it with 0.
But the comparison is fetching me wrong results.
Regardless of whether a username exists or not, it only runs the 'True' part of if.

Edit-1 The reason I used if [[ $(($USER_COUNT)) -eq 0 ]] instead of
if [$USER_COUNT -eq 0 ], as suggested by Phill, is because I was getting an error message otherwise, saying unary operator expected. I read somewhere that it is because $USER_COUNT is an unset variable.

Edit-2 Thanks for your suggestion Lennart. As suggested by him, I added -x to my first line and found that even though the value of $USER_COUNT is 1 when the user exists, and 0 otherwise, but while comparison, i.e in the step
if [[ $(($USER_COUNT)) -eq 0 ]], $USER_COUNT takes the value 0, even if earlier it was 1. Hence, it always evaluates to true.
But I am not sure what to do to resolve the above situation.

Edit-3 Thanks a lot Lennart. Your code solved my problem.

Best Answer

You may consider something like:

#!/bin/bash
...
cnt=$(psql -h localhost -U postgres -d $DB_name -At -c "SELECT COUNT(*) AS USER_COUNT FROM pg_roles WHERE rolname = '${user}' ")

if [[ ${cnt} -eq 0 ]]
then
    echo 'New User ' $user
else
    echo 'User Exists ' $user
fi

The error you got saying unary operator expected is indeed because the variable is unset. There are various ways to handle that, but in this case, I think it should be fine