NULLIF on timestamp column returning type text-CodePudding

I am trying to insert to a timestamp type where if empty string, then set value to NULL.

example: https://www.db-fiddle.com/f/p1jQfNGJ8gexUrtZgp5h63/2

However null seems to be of type text as seen by the error:

error: column "timestamp_clmn" is of type timestamp without time zone but expression is of type text

How can I use NULLIF on a timestamp field?

CodePudding user response：

From your example:

select nullif('','');
 nullif 
--------
 NULL

select pg_typeof(nullif('',''));
 

  pg_typeof 
-----------
 text

From the docs NULLIF:

The result has the same type as the first argument — but there is a subtlety. What is actually returned is the first argument of the implied = operator, and in some cases that will have been promoted to match the second argument's type. For example, NULLIF(1, 2.2) yields numeric, because there is no integer = numeric operator, only numeric = numeric.

So:

select nullif('','')::timestamp;
 nullif 
--------
 NULL

select pg_typeof(nullif('','')::timestamp);
          
     pg_typeof          
-----------------------------
 timestamp without time zone

You need to cast the text NULL to a timestamp(tz) one.