Postgre报错：ERROR: UNION types numeric and character varying cannot be matched

　　今天遇到一个问题，Postgre的一个查询功能突然报错“ERROR:  UNION types numeric and character varying cannot be matched，LINE 6: ...ipematerial,b.geom,b.pressurerating,b.pipelength, pipediam,b...”，在对两个结果集进行合并查询时报错，数字和字符串的类型不能合并，SQL如下：

SELECT
    a.gid AS gid,
    a. NAME AS NAME,
    a.pipematerial,
    a.geom,
    a.pressurerating,
    a.pipelength,
    a.pipediam,
    a.wallthickness,
    a.source,
    a.target
FROM
    zy a
WHERE
    a.projectId = '2c91808f7cc0e652017ccfc7f7ea016f'
UNION ALL
    SELECT
        b.id AS gid,
        b. NAME AS NAME,
        b.pipematerial,
        b.geom,
        b.pressurerating,
        b.pipelength,
        b.pipediam,
        b.wallthickness,
        b.source,
        b.target
    FROM
        digital_edit_pipeline b
    WHERE
        b.condition_id = '2c9180887d45f311017d55857e9b02ac'
    AND b. ENABLE = 1

　　查了一下，是digital_edit_pipeline 表的pipediam字段被改成了字符串类型，这是另外一个同事出于某种原因进行了修改，导致这个地方报错了，解决也很简单，把两个表的这个字段转换成相同的数据类型就可以了，用to_number (obj, '99999.999')就可以把字符串转数字，修改后的SQL如下：

SELECT
    a.gid AS gid,
    a. NAME AS NAME,
    a.pipematerial,
    a.geom,
    a.pressurerating,
    a.pipelength,
    a.pipediam,
    a.wallthickness,
    a.source,
    a.target
FROM
    zy a
WHERE
    a.projectId = '2c91808f7cc0e652017ccfc7f7ea016f'
UNION ALL
    SELECT
        b.id AS gid,
        b. NAME AS NAME,
        b.pipematerial,
        b.geom,
        b.pressurerating,
        b.pipelength,
        to_number (b.pipediam, '99999.999') AS pipediam,
        b.wallthickness,
        b.source,
        b.target
    FROM
        digital_edit_pipeline b
    WHERE
        b.condition_id = '2c9180887d45f311017d55857e9b02ac'
    AND b. ENABLE = 1

　　如上，就可以解决之前的报错了。如果需要截取字符串后再转数字，可以用trim()函数，如trim(both 'abc' from pipediam)，可以去除pipediam字段中包含abc的字符串，完整的书写就是to_number(trim(both 'abc' from pipediam), '9999.999') AS pipediam。

　　其实不管是Postgre还是MySQL、Oracle等其他数据，这个解决思路都是一样，差别可能就是每个数据库的函数不太一样。