Pgsql报错: invalid input syntax for integer:‘ ‘

业务sql偶尔会报错,意思是给integer了空字符串

invalid input syntax for integer:' '

起初我以为是alarm.status in () 这里传参问题,
因为我试了几次 把1换成2就不会报出这个错误,但看了很久也没发现1为什么会被认为是空字符

后来才发现,是因为类型强转的问题,应该是status为1时,camera.device_id为空了,导致强转为integer失败,因此报错;

修改前:

 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)

所以我对强转的字段加了空值转换,就不会再出现这个问题了
修改后:

 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)