Re: copy command - date

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