I am trying to import a csv file in PostgreSQL to a table. The table is set up as here:

Table Setup:

enter image description here

My csv file is delimited by commas and I have enclosed text that has commas within speech marks. It has no headers. When I go to upload the data using the commands shown I get the error shown


enter image description here

I think this may have something to do with the first column patientId being set up to automatically generate a sequential number? In my csv the numbers in this column are already sequential.

What do I need to do to get this to upload successfully please.

UPDATE - I have now changed my CSV to have headers and changed the code to only call in certain labelled columns so that is skips appId and auto generates the number as set out in the table:

COPY appointments("patientId", "appt", "staffId", "lungCapacity", "note") FROM '/home/user/VBoxShare/Appointment.csv' WITH HEADER CSV;

I am now getting this ERROR: extra data after last expected column CONTEXT: COPY appointments, line 2: "1,4,18/04/2010,4906475184,5163,," I have checked the CSV and there are no additional characters after the data. What could be causing this error?

  • I wonder if misspelling "European" is causing your problem? – DavidPostill Nov 6 '16 at 12:01
  • I was not running that line of code, but thank you, I have corrected this – Gemma Davies Nov 6 '16 at 12:02

data type serial used in column appId, is for automatically generating unique values. Try importing the .csv wihout the appId column or modify the table so that the value is set manually

  • I have tried uploading after taking out the first column data, but it is then trying to place the second column values into the first column and this is causing an error. I need the column to automatically generate a unique id as the plan would be to link it to a GUI appointment system so each needs a unique appointment id and not one manually generated. – Gemma Davies Nov 6 '16 at 12:14
  • There should be a way to specify what column from the csv goes where on the database. I am not completely sure what the syntax in postgresql is, but it should be something like this. code – user660755 Nov 6 '16 at 12:25
  • codeSET DataStyle TO European COPY appointments("patientId", "staffId", "lungCapacity") FROM '/home/user/VBoxShare/Appointment.csv' WITH CSV;code – user660755 Nov 6 '16 at 12:26
  • Thank you tapio - I have tried this, but I am still experiencing an error. Also this does not specify the columns for appt an notes. I added these into the code and I am still getting this error: – Gemma Davies Nov 6 '16 at 13:12
  • RROR: syntax error at or near "COPY" LINE 2: COPY appointments ("patientId", "staffId", "lungCapacity") ^ ********** Error ********** ERROR: syntax error at or near "COPY" SQL state: 42601 Character: 28 – Gemma Davies Nov 6 '16 at 13:17

I finally got a solution to this by adding in a header to my CSV and importing all columns as below:

COPY appointments("appId","patientId", "appt", "staffId", "lungCapacity", "note") FROM '/home/user/VBoxShare/Appointment.csv' WITH HEADER CSV;

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.