Skip site navigation (1) Skip section navigation (2)

Re: Importing from Access 2000?

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Bjorn T Johansen <btj(at)havleik(dot)no>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Importing from Access 2000?
Date: 2003-04-29 15:51:13
Message-ID: Pine.LNX.4.33.0304290949260.15964-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, 29 Apr 2003, Richard Huxton wrote:

> On Tuesday 29 Apr 2003 12:42 pm, Bjorn T Johansen wrote:
> > I am trying to convert an Access database to PostgreSQL and the import
> > works until it comes to timestamps, then I get error msg like these:
> >
> > ERROR:  Bad timestamp external representation '0000-00-00 01:00:00'
> [snip more errors]
> > ERROR:  Bad timestamp external representation '0000-00-00 19:00:00'
> >
> > Does anyone have any suggestion on how to overcome this problem?
> > (BTW, I am using PostgreSQL 7.3.2 and ODBC)
> 
> These don't appear to be valid timestamps, hence the problem. At a guess, 
> they're not actually being used to store timestamps. AFAICT your options are:
> 
> 1. Fix the data so that they are valid timestamps.
> 2. Use some other format more suited for your data (interval?)
> 3. Store your timestamp values in text in PGSQL (no, I don't like this 
> either).

Actually, I'd go with 3 as a temporary solution, then select the data into 
another table with substr or something.

i.e. 

select 
substr(baddatefield,11,8)::interval as gooddatefield, 
field2, 
field3, 
etc 
into newtable 
from oldandbusted;


In response to

pgsql-general by date

Next:From: Sergio PiliDate: 2003-04-29 16:12:40
Subject: Re: Error: No one parent tuple was found
Previous:From: scott.marloweDate: 2003-04-29 15:46:20
Subject: Re: Optimizer not using index on 120M row table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group