Re: copy command - date

Thank you! That was exactly what I was looking for =)

On 13/08/07, Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)au> wrote:
> Paul Lambert wrote:
> > novice wrote:
> >> I'm using pg version 8.2.4. What is the best method to load this data?
> >> I have just a little over 55,000 entries.
> >>
> >> db5=> \copy maintenance FROM test.txt
> >> ERROR: invalid input syntax for integer: "3665 OK SM
> >> 07/07/13 06:09"
> >> CONTEXT: COPY maintenance, line 1, column maintenance_id: "3665 OK
> >> SM 07/07/13 06:09"
> >>
> >
> > That's not complaining about the date, that is complaining that your
> > input file does not contain the maintenance_id column.
> >
>
> I don't think copy allows you to leave columns out of your input file -
> even if they belong to a sequence.
>
> You could try something like:
>
> -- Create a temp table with everything but the sequence column.
> CREATE TABLE maintenance_load AS
> SELECT meter_id,status,inspector,inspection_date
> FROM maintenance
> WHERE 1=0;
>
> -- Copy data from file into temp table.
> COPY maintenance_load FROM 'd:/temp/file.txt';
>
> -- Insert data from temp table into main table, which will
> -- generate the value for the sequence field.
> INSERT INTO maintenance (meter_id,status,inspector,inspection_date)
> (SELECT * from maintenance_load);
>
> -- Drop temp table.
> DROP TABLE maintenance_load;
>
> Also, not sure if it was your mail client or not, but the data you have
> supplied was space-separated, you probably want to make sure the actual
> data file is tab-separated, otherwise it's going to think it's all part
> of one field.
>
>
> --
> Paul Lambert
> Database Administrator
> AutoLedgers
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>