PostgreSQL – Importing CSV Data with Custom Type

copycsvpostgresql

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

Best Answer

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.

Alternative

An alternative would be to import your CSV file into a temporary copy of the target table and repair any data that would violate constraints before inserting from there. Depends on the size of the file and available RAM. You could also make this a regular table for huge files or longer processing.

CREATE TEMP TABLE tmp AS SELECT * FROM target_table LIMIT 0;
COPY tmp FROM '/path/tp/file.csv';

For instance, to remove duplicates from imported data as well as between import and existing data:

INSERT INTO target_table
SELECT DISTINCT *
FROM tmp
LEFT JOIN target_table t0 ON ...
WHERE t0.target_table_id IS NULL;

Postgresql – Importing CSV into multiple tables using PostgreSQL

I find the most reliable way to import CSV data into PostgreSQL is to create a table just for the import (you can delete it after). Often there are wonky values in some columns, so I make all the column types text and then convert them on insert into the "real" table.

The \copy command in psql (different than copy) makes it easy to copy files to a remote server, and insert into ... select makes it easy to copy to another table.

Best Answer

Related Solutions

Postgresql – Getting error while importing large data through csv

Alternative

Postgresql – Importing CSV into multiple tables using PostgreSQL

Related Question