Обсуждение: invalid abstime to timestamp error only on specific date range

I have a postgres table with a column type abstime.  (all data in this
database are loaded from an ancient propriety database)

When I filter data from this table with a specific date range on this
column it works.  When I filter with a different date range
it gives me an error like

invalid abstime to timestamp

I don't have that server at this moment and I cannot post exact error.
 I'll if I have to.

I was pulling my hair out because it happens only with a specific date
range and can't be my invalid format in select statement.

Say i do

psql> select * from shipping where ship_date <= '04/22/2008' and
ship_date>='04/21/2008'

it works but if i do

psql> select * from shipping where ship_date <= '04/22/2010' and
ship_date>='04/21/2010'

it won't even perform the query but emits the error.

Both server and client are 8.4.3

I tried other date formats but above format should work because it
works with 2008 year.

Thanks for any suggestion

zhong ming wu <mr.z.m.wu@gmail.com> writes:
> I have a postgres table with a column type abstime.  (all data in this
> database are loaded from an ancient propriety database)

> When I filter data from this table with a specific date range on this
> column it works.  When I filter with a different date range
> it gives me an error like

> invalid abstime to timestamp

> I don't have that server at this moment and I cannot post exact error.

The closest thing I can find to that in the code is

    cannot convert abstime "invalid" to timestamp

The abstime type does have a special reserved value "invalid", while
timestamp doesn't so that value can't be converted to timestamp.

I'm guessing that you have an "invalid" or two lurking in the table
somewhere, but it's hard to be specific with so few details.  You've not
shown us enough information to tell why your query would be trying to
convert any abstime values to timestamp --- let alone that particular
one.

            regards, tom lane

On Tue, Apr 27, 2010 at 11:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> zhong ming wu <mr.z.m.wu@gmail.com> writes:
>
> The closest thing I can find to that in the code is
>
>        cannot convert abstime "invalid" to timestamp

Yes this is the message.

There were invalid values in that table at first.  Then I deleted
such entries and tried the same query and got the same message.

The query is four table joins but in order to pin-point the problem
I pruned it down to as simple as what I posted originally.

In any case I reloaded the data and on this second pass
I stopped receiving the error.  The reloading scripts were
different and I don't know what that did to the reloaded data.

Thanks