UNION types text and bigint cannot be matched
Sep 21
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}]
postgresql1 answer
Accepted answer · original discussion
Sep 22
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 ...
3 question comments
Use comments to ask for clarification. Post a solution as an answer.
Sep 21
null values to bigint: select null::bigint as total_time_spent. . . To see why you get that error, try this query: with input as (select null as total_time_spent) select pg_typeof(total_time_spent) from input; It returns textSep 21
Sep 21