没有匹配给定名称和实参类型的操作符.您可能需要添加explicit

Question

我正在尝试使用bash命令行从postgresql DB获取值:有一个问题试图从表中运行select语句。

例如，如果我执行这个select语句，它返回成功并给出值

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

然而，当我试图添加更多的where语句硬编码或变量到where语句时，我得到了这个错误:

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.

脚本:

#!/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"

任何原因，即使我硬编码的值，它仍然抛出错误。有什么原因吗?

Answer 1

PostgreSQL将25理解为整数字面值，但'25'将被解释为文本字面值/字符串常量，这将与您的character varying类型列一起工作。

您可以在关闭前和打开双引号"后添加单引号'，但您也不需要关闭双引号字符串- 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'--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; "

你已经知道你可以安全地在双引号字符串中使用单引号，看看servicerequesttype='CreatepostgresCase'。

你也可以让单引号成为值的一部分:

#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; "

请记住，以这种方式构造查询总是不安全的——无论是在安全性还是便利性方面。你可以从psql -v开始改进这一点。