Today, I encountered a problem. A query function of postgre suddenly reported an error “error:   Union types numeric and character varying cannot be matched, line 6:… Ipermial, b.geom, b.pressurizing, b.pipelength, pipediam, b…., an error is reported when merging and querying two result sets. The types of numbers and strings cannot be merged. 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

Check it. It’s the digital_edit_pipeline field of the pipeline table has been changed to the string type, which was modified by another colleague for some reason, resulting in an error in this place. The solution is also very simple. Just convert the field of the two tables to the same data type. Use to_Number (obj, ‘99999.999’) can convert a 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, you can solve the previous error report. If you need to intercept a string and then turn it into a number, you can use the trim() function, such as trim (both ‘ABC’ from pipedia), which can remove the string containing ABC in the pipedia field. The complete writing is to_number(trim(both ‘abc’ from pipediam), ‘9999.999’) AS pipediam.

In fact, the solution is the same for postgre, mysql, Oracle and other data. The difference may be that the functions of each database are different.