Re: when timestamp is null

mikeo <mikeo@spectrumtelecorp.com> writes:
>   i want to update rows of a table where the column defined
> as type timestamp is null.

> update cust set cust_svc_start_dt = cust_svc_end_dt -1
> where cust_svc_start_dt is null;

> ERROR:  Unable to convert null timestamp to date

I suspect the problem here is that cust_svc_end_dt is also null in those
records, or some of them anyway, and the expression
"cust_svc_end_dt::date - 1" is what's failing.

IMHO it's a bug that the current implementation of timestamp-to-date
kicks out an error for a null timestamp; it should just play nice and
return a null date.  (This is already fixed for 7.1, BTW.)

In the meantime you could do something with a CASE expression to
substitute an appropriate result when cust_svc_end_dt is null:

UPDATE cust SET cust_svc_start_dt = CASE
    WHEN cust_svc_end_dt IS NULL THEN whatever
    ELSE cust_svc_end_dt -1
    END
WHERE ...

            regards, tom lane