Thank you! That was exactly what I was looking for =) On 13/08/07, Paul Lambert <paul.lambert@xxxxxxxxxxxxxxxxxx> 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 > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
