title: Postgresql update column of numeric type with NULL value fails if all value of this column is NULL
body: <p>I have a database table like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">idx[PK]</th>
  <th style="text-align: center;">a[numeric]</th>
  <th style="text-align: center;">b[numeric]</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">3</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">4</td>
  </tr>
  <tr>
  <td style="text-align: center;">...</td>
  <td style="text-align: center;">...</td>
  <td style="text-align: center;">...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>In pgadmin4, I tried to update this table with some null values, and I noticed the following queries failed:</p>
  <pre><code>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 
  </code></pre>
  <pre><code>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 
  </code></pre>
  <p>The error message is like this:</p>
  <pre><code>ERROR:  column &quot;a&quot; 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
  </code></pre>
  <p>However, this will be successful:</p>
  <pre><code>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 
  </code></pre>
  <p>It seems like if the new values for one of the columns I am updating are all <code>NULL</code>, then the query fails. However, as long as there is at least one value is numeric but NOT <code>NULL</code>, the query would be successful. Why is this?</p>
  <p>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 <code>NULL</code>, 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.</p>
  <p>Therefore, how to avoid this failure in my real world situation? Is there a better query to use instead of <code>UPDATE</code>?</p>
  <p>Thank you very much!</p>
  <p><strong>UPDATE</strong></p>
  <p>Per comments from @Marth and @Gordon Linoff, and as I am using psycopg2, I did the following in my code:</p>
  <pre><code>from psycopg2.extras import execute_values
  sql = &quot;&quot;&quot;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&quot;&quot;&quot;
  execute_values(cursor, sql, data)
  </code></pre>
  <p><code>cursor</code> is from the database connection. <code>data</code> is a list of tuples in the form <code>(idx, a, b)</code> of my values.</p>