I resolved it by doing this - is there another more efficient method?
And yes, the text file I am working with doesn't have any TABs
5162 OK SM 06/12/04 06:12
substr("data", 30, 2)||'-'||substr("data", 27,
2)||'-20'||substr("data", 24, 2)||substr("data", 32, 6) as
inspection_date
On 13/08/07, novice <user.postgresql@gmail.com> wrote:
> Thanks again guys =)
> I've managed to use temp table to load the data and create new table/s
> Now, how do I convert a text field with 'YY/MM/DD' to date field 'DD/MM/YY'?
>
> On 13/08/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Paul Lambert <paul.lambert@autoledgers.com.au> writes:
> > >> novice wrote:
> > >>> db5=> \copy maintenance FROM test.txt
> >
> > > I don't think copy allows you to leave columns out of your input file -
> > > even if they belong to a sequence.
> >
> > Well, it does, but you have to specify which ones are being provided,
> > eg \copy tab(col1,col4,col7, ...
> >
> > But the long and the short of it is that COPY doesn't see any column
> > delimiters at all in this file. We're guessing as to what the OP
> > intends the columns to be, but whatever he wants, he needs something
> > other than an uncertain number of spaces to separate them ...
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
>