业务sql偶尔会报错,意思是给integer了空字符串
invalid input syntax for integer:' '
起初我以为是alarm.status in () 这里传参问题,
因为我试了几次 把1换成2就不会报出这个错误,但看了很久也没发现1为什么会被认为是空字符
后来才发现,是因为类型强转的问题,应该是status为1时,camera.device_id为空了,导致强转为integer失败,因此报错;
修改前:文章来源：https://www.toymoban.com/news/detail-803129.html
select alarm.*,camera.status as camera_status, region.name
from ai.alarm_log alarm
left join ai.camera on alarm.camera_id = camera.id
left join vcenter.device d on d.id = camera.device_id::INTEGER left join ai.region on region.id = d.org_id::BIGINT
WHERE alarm.start_time BETWEEN to_timestamp(1692806400) AND to_timestamp(1693497599)
AND alarm.status in (0,1)
所以我对强转的字段加了空值转换,就不会再出现这个问题了
修改后:文章来源地址https://www.toymoban.com/news/detail-803129.html
select alarm.*, camera.status as camera_status, region.name
from ai.alarm_log alarm
left join ai.camera on alarm.camera_id = camera.id
left join vcenter.device d on d.id = COALESCE(NULLIF(camera.device_id,''),'0')::INTEGER
left join ai.region on region.id = COALESCE(NULLIF(d.org_id,''),'0')::BIGINT
WHERE alarm.start_time BETWEEN to_timestamp(1692806400) AND to_timestamp(1693497599) AND alarm.status in (0,1)
到了这里，关于Pgsql报错: invalid input syntax for integer:‘ ‘的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
