Erik Sandberg Erik Sandberg - 4 years ago 63
SQL Question

Why does PostgreSQL complain about type when I use CASE to SET a field to NULL?

I have the following query:

UPDATE managed_avs
SET own_license_expires_at = CASE id WHEN 50 THEN NULL END
WHERE id in (50)


I get the following error:

ERROR: column "own_license_expires_at" is of type timestamp without time zone but expression is of type text
LINE 1: update managed_avs set own_license_expires_at = CASE id WHEN...
^
HINT: You will need to rewrite or cast the expression.


Why does it say that
CASE id WHEN 50 THEN NULL END
is of type text? Isn't it just
NULL
?

Answer Source

This happens because the case expression returns a null value for every possible outcome. As a null value has no type, Postgres defaults to text.

You can verify that using pg_typeof():

select pg_typeof(case id when 50 then null end)
from (values (50) ) as x (id);

returns

pg_typeof
---------
text     

In order for this to work either the result of the when needs to be cast to a timestamp or the whole expression:

case id when 50 then null::timestamp end

or

(case id when 50 then null end)::timestamp
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download