Thread: 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 >