No operator matches the given name and argument types. You might need to add explicit-CodePudding

I am trying to get values from postgresql DB using the bash command line : There is an issue trying to get run a select statement from the table.

For example if I execute this select statement, it return successful and gives the value

psql -U postgres -d postgres -p 5432 -t -c "select count(*) from sampledata.sif_work where servicerequesttype='CreatepostgresCase'"

However when I tried to add more where statement either hardcoded or variables to the WHERE statement, I got this error :

ERROR:  operator does not exist: character varying <> integer
LINE 1: ...questtype='CreatepostgresCase' and applicationerrorcode!=25 and a...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

The script :

 #!/bin/bash
errorCodeSuccess=0
errorCodeFailure=30
sampleDbUser=postgres
sampleDBPort=5432

appErrorCodeFailure=25

#hardcoded
psql -U postgres -d postgres -p 5432 -t -c "select count(*) from sampledata.sif_work where servicerequesttype='CreatepostgresCase' and applicationerrorcode=25  and pxcreatedatetime>current_date"

#variables used 
psql -U "${sampleDbUser}" -d postgres -p "${sampleDBPort}" -t -c "select count(*) from sampledata.sif_work where servicerequesttype='CreatepostgresCase' and applicationerrorcode!="${appErrorCodeFailure}" and applicationerrorcode!="${errorCodeSuccess}"  and pxcreatedatetime>current_date"

Any reason why even though I hardcoded the value, it is still throwing error. Any reason ?

CodePudding user response：

PostgreSQL understands 25 as an integer literal but '25' will be interpreted as a text literal/string constant, which would work with your character varying type column.

You could add the single quote ' before you close and after you open the double quotes ", but you also don't need to close that double-quoted string at all - bash evaluates $ expressions in double quotes:

errorCodeSuccess=0
errorCodeFailure=30
sampleDbUser=postgres
sampleDBPort=5432

appErrorCodeFailure=25

#hardcoded
psql -U postgres -d postgres -p 5432 -t \
    -c "select count(*) 
        from   sampledata.sif_work 
        where  servicerequesttype='CreatepostgresCase' 
        and    applicationerrorcode='25'--single quotes indicate a text literal
        and    pxcreatedatetime>current_date"

#variables used 
psql -U "${sampleDbUser}" -d postgres -p "${sampleDBPort}" -t \
    -c "select count(*)
        from   sampledata.sif_work
        where  servicerequesttype='CreatepostgresCase'
        and    applicationerrorcode!='${appErrorCodeFailure}'
        and    applicationerrorcode!='${errorCodeSuccess}'
        and    pxcreatedatetime>current_date; "

You already knew you can safely use single quotes within a double-quoted string, looking at servicerequesttype='CreatepostgresCase'.

You can also make the single quotes a part of the value:

#already doesn't work:
errorCodeSuccess=0
#same effect:
errorCodeSuccess='0'  
#this will be interpreted as a column named "0":
errorCodeSuccess='"0"' 
#"0" would be a valid name, but I doubt you have one or want one

#this will work:
errorCodeSuccess="'0'"
errorCodeFailure="'30'"
sampleDbUser=postgres
sampleDBPort=5432
psql -U "${sampleDbUser}" -d postgres -p "${sampleDBPort}" -t \
    -c "select count(*)
        from   sampledata.sif_work
        where  servicerequesttype='CreatepostgresCase'
        and    applicationerrorcode != ${appErrorCodeFailure}
        and    applicationerrorcode != ${errorCodeSuccess}
        and    pxcreatedatetime>current_date; "

Keep in mind that it's always unsafe to construct queries this way - both in terms of security and convenience. You could start improving this with psql -v.