postgresql - Importing CSV Data with Custom Type

Question

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

Answer 1

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.