Postgresql - Interpreted type for NULL is wrong

Question

I have the problem with the following CTE expression because prev_count in new_values is being interpreted as text, but the column I'm updating in counts is type integer. I'm getting this error on the marked line:

ERROR:  column "prev_count" is of type integer but expression is of type text
LINE 12:     prev_count = new_values.prev_count

Here's the query:

WITH  
    new_values (word,count,txid,prev_count) AS (
        VALUES ('cat',1,5,NULL)),
    updated AS (
        UPDATE 
            counts t 
        SET 
            count = new_values.count, 
            txid = new_values.txid, 
            prev_count = new_values.prev_count -- ERROR HERE
        FROM 
            new_values 
        WHERE (
            t.word = new_values.word
        ) 
    RETURNING t.*) 
INSERT INTO counts(
    word,count,txid,prev_count
) SELECT 
    word,count,txid,prev_count FROM new_values 
WHERE NOT EXISTS (
    SELECT 1 FROM updated WHERE (updated.word = new_values.word))

My question is, what's an elegant way to fix the error? I would rather specify the type of prev_count in new_values instead of adding an explicit cast, but I don't see anything like that in the docs.

Maybe this question would be better suited to dba.stackexchange.com? — dreamlax
– dreamlax, Commented Apr 28, 2016 at 9:51

Chris Travers · Accepted Answer · 2016-05-01 14:58:43Z

9

Adding this here as an explicit answer along with a detailed explanation.

The fix is:

WITH  
    new_values (word,count,txid,prev_count) AS (
        VALUES ('cat',1,5,NULL::text)),

As a_horse_with_no_name suggested in the comments.

Why is this necessary? Because the row specification comes from the VALUES section and NULL is unknown. In this case PostgreSQL helpfully casts to text. But that is not what you want so you have to give a type to the NULL.

This often comes up in other cases too, such as UNION statements where a NULL in the first segment in the column list can be given an implicit type which clashes with the type of the column in another segment. So this is a tricky corner worth knowing about.

answered May 1, 2016 at 14:58

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nathan Long Over a year ago

It had never occurred to me that NULL values still have the column's type, but I guess PostgreSQL represents "missing text value" different than "missing integer value" or whatever? Anyway, this just helped me solve a similar problem where I had to select NULL::boolean

Chris Travers Over a year ago

@NathanLong The issue is that the tuple type has to specify things like memory allocation. In this case that doesn't matter but the shared infrastructure requires it.

Collectives™ on Stack Overflow

Postgresql - Interpreted type for NULL is wrong

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related