[SOLVED] UNION types text and bigint cannot be matched

UNION types text and bigint cannot be matched

I'm running a complex stored procedure and I'm getting an error when I have 3 unions, but with 2 unions no error. If I remove either of the top two unions it runs fine. If I make one of the NULLs a 0, it runs fine. The error is "UNION types text and bigint cannot be matched"

```lang-sql
SELECT NULL AS total_time_spent 
FROM tbl1
GROUP BY student_id 
UNION ALL 
SELECT NULL AS total_time_spent
FROM tbl2
GROUP BY student_id 
UNION ALL 
SELECT sum(cast(("value" ->> 'seconds') AS integer)) AS total_time_spent 
FROM tbl3 
GROUP BY student_id
```

I've tried all kinds of casting on the sum result or the sum input. The json that I'm pulling from is either NULL, [] or something like this:

[{"date": "2020-09-17", "seconds": 458}]

Solution

According to the SQL standard, the NULL value exists in every data type, but lacking an explicit type cast, the first subquery resolves the data type to to text (earlier versions of PostgreSQL would have used unknown here, but we don't want this data type in query results).

The error message is then a consequence of the type resolution rules for UNION in PostgreSQL.

Use an explicit type case to avoid the problem:

SELECT CAST(NULL AS bigint) FROM ...
UNION ...