19

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}]
Stephane
  • 1,613
  • 5
  • 20
  • 40
  • 8
    cast your `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 `text` – Mike Organek Sep 21 '20 at 17:38
  • And that won't result in the value being 0? Amazing! – Stephane Sep 21 '20 at 21:24
  • 1
    No. There is no interpolation or assumption of another value for actual `null` values. The problem was that a `null` without context is assumed to be `null::text`. The first `select` in the `union` therefore has that column defined as `null`, which causes the `union` to fail when your third `select` puts a `bigint` value in that column. – Mike Organek Sep 21 '20 at 21:34
  • I did try casting it before but I thought it returned zero. Thanks again for your help. – Stephane Sep 22 '20 at 05:25

1 Answers1

36

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 ...
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263