Question 90617 in PostgreSQL Question posted in
The official documentation can be found here.

Using WHERE with multiple columns with different data types to satisfy a single input in bash and postgressql – Postgresql

please assist with the following. i m trying to run a script that accepts one argument $1. The argument can either be a string or character or an integer. I want to use the argument in there where clause to search for the element in the database.

This is the table i want to search from:enter image description here

When i use the multiple conditions with OR , it works only when either the argument is a number or text.
This what my code looks like enter image description here
`

ELEMENT=$($PSQL "SELECT * FROM elements e FULL JOIN properties p USING(atomic_number) WHERE symbol = '$1' OR name = '$1' OR atomic_number = $1;")

`

and this is the results i get when i run with different aurgumentsenter image description here

Please help.

Thank you in advance

Tags:

2

Answers


  1. 0

    This will always fail on any non-numeric argument.

    You are passing in H for hydrogen, but taking whatever was passed in and using it in the atomic_number comparison as an unquoted number, which the DB engine is trying to figure out what to do with. H isn’t a number, and isn’t a quoted string, so it must be the name of a column…but it isn’t, so you are using invalid syntax.

    I don’t have a postgres available right now, but try something like this –

    ELEMENT=$( $PSQL "
      SELECT * 
        FROM elements e 
   FULL JOIN properties p USING(atomic_number) 
       WHERE symbol = '$1' 
          OR name = '$1' 
          OR atomic_number = CAST(`$1` as INTEGER); " )

    Also, as an aside… avoid all-capital variable names.
    As a convention, those are supposed to be system vars.

    And please – please don’t embed images except as helpful clarification.
    Never rely on them to provide info if it can be avoided. Copy/paste actual formatted text people can copy/paste in their own testing.

    Login or Signup to reply.
  2. 0

    An alternate way to construct the query: requires 

    looks_like_a_number() {
    # only contains digits
    [[ "$1" == +([[:digit:]]) ]]
}

sanitize() {
    # at a minimum, handle embedded single quotes
    printf '%s' "${1//'/''}"
}

if looks_like_a_number "$1"; then
    field="atomic_number"
    value=$1
elif [[ ${#1} -eq 1 ]]; then
    field="symbol"
    printf -v value "'%s'" "$(sanitize "$1")"
else
    field="name"
    printf -v value "'%s'" "$(sanitize "$1")"
fi

q="SELECT *
   FROM elements e
   FULL JOIN properties p USING(atomic_number)
   WHERE $field = $value;"

printf '%sn' "$q"

result=$("$PSQL" "$q")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search