Postgresql update column of numeric type with NULL value fails if all value of this column is NULL

user_16996548 0
Viewed 813

I have a database table like this:

idx[PK] a[numeric] b[numeric]
1 1 1
2 2 2
3 3 3
4 4 4
... ... ...

In pgadmin4, I tried to update this table with some null values, and I noticed the following queries failed:

UPDATE test as t SET
a = e.a,b = e.b
FROM (VALUES (1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL)) 
AS e(idx, a, b)
WHERE t.idx = e.idx 

UPDATE test as t SET
a = e.a,b = e.b
FROM (VALUES (1,NULL,1),(2,NULL,2),(3,NULL,NULL)) 
AS e(idx, a, b)
WHERE t.idx = e.idx

The error message is like this:

ERROR:  column "a" is of type numeric but expression is of type text
LINE 2: a = e.a,b = e.b
            ^
HINT:  You will need to rewrite or cast the expression.
SQL state: 42804
Character: 43

However, this will be successful:

UPDATE test as t SET
a = e.a,b = e.b
FROM (VALUES (1,NULL,1),(2,2,NULL),(3,NULL,NULL)) 
AS e(idx, a, b)
WHERE t.idx = e.idx

It seems like if the new values for one of the columns I am updating are all NULL, then the query fails. However, as long as there is at least one value is numeric but NOT NULL, the query would be successful. Why is this?

I did simplify my real world case here as my actual table has millions of rows and more than 10 columns. Using Python and psycopg2, when I tried to update 50,000 rows in one query, even though there is a value in a column is NOT NULL, the previous error could still show up. I guess that is because the system scans a certain number of rows to decide if the type is correct or not instead of all 50,000 rows.

Therefore, how to avoid this failure in my real world situation? Is there a better query to use instead of UPDATE?

Thank you very much!

UPDATE

Per comments from @Marth and @Gordon Linoff, and as I am using psycopg2, I did the following in my code:

from psycopg2.extras import execute_values
sql = """UPDATE test as t SET
a = (e.a::numeric),
b = (e.b::numeric)
FROM (VALUES %s) 
AS e(idx, a, b)
WHERE t.idx = e.idx"""
execute_values(cursor, sql, data)

cursor is from the database connection. data is a list of tuples in the form (idx, a, b) of my values.

pgadmin-4 psycopg2 postgresql
1 Answers

This is due to the default behavior of how NULL works in these situations. NULL is generally an unknown type, which is then treated as whatever type is necessary.

In a values() statement, Postgres tries to decipher the types. It treats the individual records as it would with a union. But if all are NULL . . . well, then there is no information. And Postgres decides on using text as the universal default.

It is also important to understand that this fails with the same error:

UPDATE test t
    SET a = ''
    WHERE t.id = 1;

The issue is that Postgres does not convert empty strings to numbers (unlike some other databases).

In any case, this is easily fixed by casting the NULL to an appropriate type:

UPDATE test t
    SET a = e.a,b = e.b
    FROM (VALUES (1, NULL::numeric, NULL::numeric),
                 (2, NULL, NULL),
                 (3, NULL, NULL)
         ) e(idx, a, b)
WHERE t.idx = e.idx ;

You can be explicit for all occurrences of NULL, but that is not necessary.

Here is a db<>fiddle that illustrates some of this.

user_11440350
Related