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
