PostgreSQL执行统计SQL遇类型不匹配错误，需显式类型转换

错误原因 #

你执行的SQL中，UBVALUEDTTM字段是timestamp without time zone类型，而LIKE是仅适用于字符串类型的操作符，直接用字符串和timestamp类型做匹配，PostgreSQL找不到对应的操作符，因此抛出operator does not exist: timestamp without time zone ~~ unknown错误（~~是PostgreSQL对LIKE的内部标识）。

解决方案 #

根据你的需求，提供三种可行的修正写法：

1. 精确匹配指定时间点

如果目标是匹配2015-11-07 00:00:00这个精确时间，直接将字符串转为timestamp类型后用=匹配：

SELECT COUNT(*) 
FROM WASADMIN.DAILYTXNSREPORT 
WHERE UBACCOUNTID = '01ED10EOD0100' 
  AND UBTXNAMT = '109.63' 
  AND UBTYPE = 'I' 
  AND UBVALUEDTTM = TIMESTAMP '2015-11-07 00:00:00'
  AND UBTXNAMTCR = '109.63' 
  AND UBTXNAMTDR = '0.0' 
  AND UBTXNCODE = 'IAP' 
  AND UBTXNNARRATION = 'Fixed Narrative:Interest Application' 
  AND UBTXNSRCBRANCH = '70000001.0' 
  AND UBTXNBASEEQ = '109.63' 
  AND UBCHANNELID = 'UXP'

2. 匹配指定日期全天的记录

如果原SQL的LIKE '%'是想匹配2015年11月7日全天的所有记录，用范围查询更高效（还能利用字段索引）：

SELECT COUNT(*) 
FROM WASADMIN.DAILYTXNSREPORT 
WHERE UBACCOUNTID = '01ED10EOD0100' 
  AND UBTXNAMT = '109.63' 
  AND UBTYPE = 'I' 
  -- 推荐写法：匹配2015-11-07 00:00:00到2015-11-08 00:00:00之前的所有记录
  AND UBVALUEDTTM >= DATE '2015-11-07' 
  AND UBVALUEDTTM < DATE '2015-11-08'
  AND UBTXNAMTCR = '109.63' 
  AND UBTXNAMTDR = '0.0' 
  AND UBTXNCODE = 'IAP' 
  AND UBTXNNARRATION = 'Fixed Narrative:Interest Application' 
  AND UBTXNSRCBRANCH = '70000001.0' 
  AND UBTXNBASEEQ = '109.63' 
  AND UBCHANNELID = 'UXP'

3. 强制字符串模糊匹配（不推荐大表使用）

如果确实需要对时间格式做模糊匹配（比如匹配某小时的所有记录），可以将timestamp转为指定格式的字符串后再用LIKE，但这种方式会导致索引失效，大表查询效率极低：

SELECT COUNT(*) 
FROM WASADMIN.DAILYTXNSREPORT 
WHERE UBACCOUNTID = '01ED10EOD0100' 
  AND UBTXNAMT = '109.63' 
  AND UBTYPE = 'I' 
  -- 将timestamp转为MM/DD/YYYY  HH:MI:SS AM格式的字符串后匹配
  AND TO_CHAR(UBVALUEDTTM, 'MM/DD/YYYY  HH:MI:SS AM') LIKE '11/07/2015  12:00:00 AM%'
  AND UBTXNAMTCR = '109.63' 
  AND UBTXNAMTDR = '0.0' 
  AND UBTXNCODE = 'IAP' 
  AND UBTXNNARRATION = 'Fixed Narrative:Interest Application' 
  AND UBTXNSRCBRANCH = '70000001.0' 
  AND UBTXNBASEEQ = '109.63' 
  AND UBCHANNELID = 'UXP'

内容的提问来源于stack exchange，提问作者Aishwarya