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

error¶

Encountered a problem today, a query function of Postgre suddenly reported an error

ERROR: UNION types numeric and character varying cannot be matched, LINE 6: ...ipematerial,b.geom,b.pressurerating,b.pipelength, pipediam,b...

An error is reported when performing a combined query on the two result sets, and the types of numbers and strings cannot be combined. The SQL is as follows:

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

reason and solution¶

I checked it and found that the pipediam field of the digital_edit_pipeline table was changed to a string type. This is because another colleague made a modification for some reason, which caused an error in this place. The solution is also very simple. This field of the table can be converted to the same data type. Use to_number (obj, '99999.999') to convert the string to a number. The modified SQL is as follows:

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

As above, the previous error can be resolved. If you need to intercept a string and then convert it to a number, you can use the trim() function, such as trim(both'abc' from pipediam), to remove the string containing abc in the pipediam field, and write it completely that is

to_number(trim(both'abc' from pipediam), '9999.999') AS pipediam.

In fact, whether it is Postgre or other data such as MySQL or Oracle, the solution is the same. The difference may be that the functions of each database are different.