Postgres Pro
Downloads
Home   >   mailing lists

Thread: Interval formats in select and dump

Interval formats in select and dump

From
"Gurunandan R. Bhat"
Date:
Hi,

    Whenever I dump tables with an interval field which is zero,
pg_dump writes it as "@ 0" rather than "@ 0 hours" (which is the case with
intervals whose values are not zero). When this happens, the restore
always dies with a format error. Until now I have been working around this
by editing the dump by hand and adding the string "hours" after a "@ 0\t",
but I am sure there must be a more intelligent way. Any suggestions?

    IAC, is there some way of excluding the "@" in the interval field
in the output of a select statement?

    I am using European,Postgres datestyle and postgres is 7.1.3

Thanks and regards,
Gurunandan

Re: Interval formats in select and dump

From
Tom Lane
Date:
"Gurunandan R. Bhat" <grbhat@exocore.com> writes:
>     Whenever I dump tables with an interval field which is zero,
> pg_dump writes it as "@ 0" rather than "@ 0 hours" (which is the case with
> intervals whose values are not zero). When this happens, the restore
> always dies with a format error.

This seems to be fixed in current sources (7.2beta).  '@ 0' should be
legal input, and is accepted now.

>     IAC, is there some way of excluding the "@" in the interval field
> in the output of a select statement?

Don't use Postgres datestyle?

            regards, tom lane

Re: Interval formats in select and dump

From
"arguile"
Date:
I don't have a solution as far as Postgres goes, but if you want to easily
massage the dumpfile.

    perl -i.bak -pe 's/@ 0\t/@ 0 hours\t/' <filename>

Just replace <filename> with the.. well filename. :)

Gurunandan R. Bhat wrote:
>
> Hi,
>
>     Whenever I dump tables with an interval field which is zero,
> pg_dump writes it as "@ 0" rather than "@ 0 hours" (which is the case with
> intervals whose values are not zero). When this happens, the restore
> always dies with a format error. Until now I have been working
> around this
> by editing the dump by hand and adding the string "hours" after a
> "@ 0\t",
> but I am sure there must be a more intelligent way. Any suggestions?
>
>     IAC, is there some way of excluding the "@" in the interval field
> in the output of a select statement?
>
>     I am using European,Postgres datestyle and postgres is 7.1.3
>
> Thanks and regards,
> Gurunandan
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> >