when timestamp is null

Started by mikeoalmost 26 years ago3 messagesgeneral
Jump to latest
#1mikeo
mikeo@spectrumtelecorp.com

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 |

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: mikeo (#1)
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

#3mikeo
mikeo@spectrumtelecorp.com
In reply to: Tom Lane (#2)
Re: when timestamp is null

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:

Show quoted text

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