1

I'm trying to update a nullable date column with NULL and for some reason Postgres takes NULL as text and gives the below error

 UPDATE tbl
 SET
 order_date = data.order_date
 FROM
 (VALUES (NULL, 100))
 AS data(order_date,id)
 WHERE data.id = tbl.id

And the error shows:

[42804] ERROR: column "order_date" is of type date but expression is of type text
Hint: You will need to rewrite or cast the expression.

I can fix this by explicitly converting NULL to date as below:

NULL::date

But, Is there a way to achieve this without explicit type conversion?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
findissuefixit
  • 121
  • 2
  • 6

1 Answers1

4

You can avoid the explicit cast by copying data types from the target table:

UPDATE tbl
SET    order_date = data.order_date
FROM  (
   VALUES
    ((null::tbl).order_date, (null::tbl).id)
    (null, 100)
   ) data(order_date, id)
WHERE  data.id = tbl.id;

This way, you don't have to know column data types.

The added dummy row with null values is filtered by WHERE data.id = tbl.id.

Related answer with detailed explanation:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228