Обсуждение: when timestamp is null

hi,

  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;


<bold>ERROR:  Unable to convert null timestamp to date

</bold>

how can i get around this?  i've tried to_char and casting

as date, time, etc.  to no avail.


TIA,

  mikeo


\d cust

                Table "cust"

     Attribute     |    Type     | Modifier

-------------------+-------------+----------

 cust_id           | varchar(15) | not null

 cut_id            | varchar(6)  |

 cust_name         | varchar(50) |

 cust_division     | varchar(6)  |

 cust_svc_start_dt | timestamp   |

 cust_svc_end_dt   | timestamp   |

 cust_valid        | varchar(1)  |

 cust_bill_loc_id  | varchar(6)  |

 wu_id             | varchar(10) |

 cust_timestamp    | timestamp   |

 agt_id            | integer     |

 rse_id            | integer     |

 bd_id             | varchar(6)  |

 cust_email        | varchar(50) |

 cust_stream       | integer     |

 br_cycle          | bigint      |

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

thank you very much, that worked wonderfully.
i didn't even think about the end date being null.

mikeo


At 04:16 PM 7/12/00 -0400, Tom Lane wrote:
>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
>