Thread

1. when timestamp is null

   mikeo <mikeo@spectrumtelecorp.com> — 2000-07-12T19:40:27Z

   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      | 
   
   

2. Re: when timestamp is null

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-12T20:16:53Z

   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
   
   

3. Re: when timestamp is null

   mikeo <mikeo@spectrumtelecorp.com> — 2000-07-12T20:56:20Z

   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
   >