Checking the result of a postgres query from Bash

Question

I'm having issues parsing the outputs of a postgres sql query from bash script.

Here is the output from a command line query to postgres:

root@API:/home# psql -U postgres -h pgipaddress -c "SELECT 1 AS __ROW__ FROM pg_roles WHERE rolname='postgres'"
 __row__ 
---------
       1
(1 row)

Here is what the variable $userexists looks like before you look at the conditions:

__row__ --------- 1 (1 row)

Here are my attempts at string conditional checking:

userexists=$(psql -U postgres -h ${pgip} -c "SELECT 1 AS __ROW__ FROM pg_roles WHERE rolname='postgres'")
if [ "${userexists}" == "__row__\n---------\n1 \n(1 row)"* ] ; then
  printf "Database User exists\n"
else
  printf "Database User does not exists\n"
fi

userexists=$(psql -U postgres -h ${pgip} -c "SELECT 1 AS __ROW__ FROM pg_roles WHERE rolname='postgres'")
if [ "${userexists}" == "__row__ --------- 1 (1 row)"* ] ; then
  printf "Database User exists\n"
else
  printf "Database User does not exists\n"
fi

All result in database user does not exist.

Have you considered using --quiet -tA flags, which would reduce your output to just 1 and nothing else? — Benjamin W.
– Benjamin W., Commented Nov 14, 2018 at 18:34
perfect works great. only problem is if the user does not exist postgres issues an error on the console. i tried redirecting errors to /dev/null but when i do that i dont get output anymore — somejkuser
– somejkuser, Commented Nov 14, 2018 at 18:37
actually that error was separate. make this an answer and ill accept — somejkuser
– somejkuser, Commented Nov 14, 2018 at 18:47

codeforester · Accepted Answer · 2018-11-14 19:07:22Z

The contents of userexists are likely to actually contain the linebreaks, but you probably checked the contents with an unquoted expansion:

# Assignment
$ userexists=' __row__
---------
       1
(1 row)'

$ echo "$userexists"  # Quoted expansion
 __row__
---------
       1
(1 row)

$ echo $userexists    # Unquoted expansion squashes whitespace
__row__ --------- 1 (1 row)

Additionally, if you want to compare two strings containing linebreaks with [ ... ], you can't use "\n" to insert a linebreak. ANSI-C escapes, $'\n', would work:

var='has
linebreak'
[ "$var" = $'has\nlinebreak' ] && echo 'Match'

prints Match. Two remarks: Bash will understand == when used in [ ], but it's not portable, so = is recommended. Also, [ ] doesn't support pattern matching, so the * at the end of your right-hand side won't do what you expect. [[ ]] and case can be used for pattern matching.

To avoid the whole problem in the first place, you can supply a few flags to psql, specifically:

--quiet – no informational output
-t – tuples only; no column names, result count footers etc.
-A – unaligned output (removes leading blank from line)

and the return from your command will be just 1 and you can compare with

if [ "$userexists" = 1 ]; then

Collectives™ on Stack Overflow

Checking the result of a postgres query from Bash

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related