Error UNION types %s and %s cannot be matched Is Reported

Symptom

The error UNION types %s and %s cannot be matched is reported when the UNION statement is executed.

Possible Causes

In the UNION branch, the formats of the output columns in the same position are different.

Handling Procedure

Fault construction

1. Use the client to connect to the GaussDB(DWS) database.
2. Run the following SQL statements:

   1 2 3 4 5 6 7 8 9 10 11 12

   CREATE TABLE t1(a int, b timestamp); CREATE TABLE CREATE TABLE t2(a int, b text); CREATE TABLE INSERT INTO t1 select 1, current_date; INSERT 0 1 INSERT INTO t2 select 1, current_date; INSERT 0 1 SELECT * FROM t1 UNION SELECT * FROM t2; ERROR: UNION types timestamp without time zone and text cannot be matched LINE 1: SELECT * FROM t1 UNION SELECT * FROM t2; ^

Solution

1. In the example, the types of column b in tables t1 and t2 are different. As a result, a type mismatch error is reported during the UNION operation. Ensure that the types of the output columns in the same position of each UNION branch match.
   

   Column b in the table t2 is of the text type, and the inserted data is current_date. During the insertion, implicit type conversion is automatically performed. Therefore, no error is reported during the insertion. However, during the query, implicit conversion is not automatically performed. As a result, an error is reported.

   To solve the preceding problem, ensure that the output column types of each UNION branch match. If they do not match, forcibly convert the output column types.

   1 2 3 4 5 6

   SELECT a,b::text FROM t1 UNION SELECT a,b FROM t2; a | b ---+--------------------- 1 | 2023-02-16 1 | 2023-02-16 00:00:00 (2 rows)