Re: date/time compatible problems in 7.2 - Mailing list pgsql-hackers

> I has pg_dump my DB in 7.1.3 and try ro pg_restore it in 7.2
> version.
> psql:/.../dbdump/.dbrestore.tmp:1624094: ERROR:  copy: line 1, Bad
> timestamp external representation 'Fri 25 Jan 23:59:59 2002 KRAT'
> psql:/.../dbdump/.dbrestore.tmp:1624094: lost synchronization with
> server, resetting connection

Not sure why it is crashing. But "KRAT" is a time zone not recognized by
the PostgreSQL date/time parser. In fact it could be afaik (it is
mentioned but commented-out in the parser) but it either had a screwy
definition or I couldn't figure out what the definition was. It could be
added for 7.2.1 (and I could send a patch beforehand) if I knew the
proper definition. Check src/backend/utils/adt/datetime.c and look for
"krat".

>         1. Function time(datetime) don't exists in 7.2?
>            SELECT time('now');   processed ok in 7.1.3, but 7.2 says:
>            parser: parse error at or near "'".

Right. 7.2 implements the SQL99 feature of time precision, so "time()"
now indicates a data type, not a function call. Same for "timestamp()".

select time 'now'
or
select cast('now' as time)

is the preferred syntax for your use case anyway.

>         2. CREATE TABLE akka (tm TIMESTAMP WITH TIME ZONE);
>            SET datestyle TO postgresql,european;
>            INSERT INTO akka VALUES ('akka');
>            INSERT INTO akka SELECT tm::text FROM akka;     -- *
>            Last SQL processed well in 7.1.3, but in 7.2 didn't:
>            ERROR:  Bad timestamp external representation 'Thu 07 Feb
> 16:36:50.730499 2002 KRAT'

Ah! 7.1 and earlier was forgiving of junk strings in date/time values,
and just ignored them on input (this was for historical reasons only,
dating back to at least Postgres95 and probably earlier). But that would
open us up to unintended data if, for example, someone mistyped a time
zone field which would then be ignored as junk. So junk is no longer
ignored except in a few specific cases. I believe that the docs cover
the parsing rules, including the changes for 7.2.

I'm a little suprised that input completely devoid of information as in
example (2) above was actually accepted by 7.1. In fact it isn't:

lockhart=# CREATE TABLE akka (tm TIMESTAMP WITH TIME ZONE);
CREATE
lockhart=# INSERT INTO akka VALUES ('akka');
ERROR:  Bad timestamp external representation 'akka'
lockhart=# select version();                          version                           
-------------------------------------------------------------PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC
2.96

But if there is some valid info in the input then it was accepted prior
to 7.2:

lockhart=# INSERT INTO akka VALUES ('now akka');
INSERT 26953 1

hth
                      - Thomas