From: | mikeo <mikeo(at)spectrumtelecorp(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: when timestamp is null |
Date: | 2000-07-12 20:56:20 |
Message-ID: | 3.0.1.32.20000712165620.0090b1f0@pop.spectrumtelecorp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)spectrumtelecorp(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | jeam | 2000-07-12 21:05:07 | The right direction for backups? |
Previous Message | Tom Lane | 2000-07-12 20:16:53 | Re: when timestamp is null |