2

I'm mostly a newcomer to databases and am building a postgres database of student achievement in a series of courses. My database uses a type called "class" that consists of the course, the academic term (year and quarter), and the section number (1,2,3,...), e.g. ('Bio 101', (17, 'F'), 1). I'm trying to copy a CSV containing such data into a table and am getting an error message saying ERROR: extra data after last expected column. I've tried the pgAdmin III import wizard, COPY and \copy, all with the same results. However, manually copying a line from the file and IMPORTing it (using the appropriate parentheses) works fine. What's going on and what can I do?

Here are the table and some data:

CREATE TABLE public."GradedItems"
(
  class class NOT NULL,
  category text NOT NULL,
  "number" smallint NOT NULL DEFAULT 1,
  topic text[],
  points integer,
  weight double precision, -- Weight of item within category
  CONSTRAINT primary_key PRIMARY KEY (class, category, number)
)


'class','category','number','points'
('LS 30A', (18, 'W'), 2),'Homework',1,60
('LS 30A', (18, 'W'), 2),'Homework',2,36
  • 1
    fo You have any ways for modify Your CSV files to proper format? how You prepare them? You need - add for export enclosing by "" or change separator to TAB – a_vlad Jun 19 '18 at 3:22
  • Yes, I do. Text fields are already enclosed by "". Are you saying I should enclose each field this way and switch to a tab separator? – jaia Jun 26 '18 at 2:06
  • May be you can show the copy commands that you tried, and the class type definition. – mustaccio Jul 6 '18 at 1:22
1
+50

What is going on is a mismatch on the number of columns names and the number of columns in your data. Without information on how the csv is generated and how you are importing it is hard to say, but presuming the separator on your csv is the comma character, this is what the database insert operation is trying to do: it reads this 'class','category','number','points' and learns how to map four columns. Then, when it gets to the second line, it is splitting the data by the commas, so it interprets that line as

('LS 30A'||(18||'W')|| 2)||'Homework'||1||60

where the || signifies a break between values. It interprets the command as:

set 
  class = ('LS 30A',
  category = (18,
  number = 'W',
  points = 2)

...and then it does not know what to do with the rest of the data, as it does not fit.

My suggestion would be to use a different separator. It will depend on how the csv is being generated how you do that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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