15

I use basketball data tables to get some understanding of Postgres 9.2 & phppgadmin. Therefore I would like to import csv tables into that database. However, I get:

ERROR:  missing data for column "year"
CONTEXT:  COPY coaches, line 1: ""coachid";"year";"yr_order";"firstname";"lastname";"season_win";"season_loss";"playoff_win";"playoff..."

with command:

\copy coaches FROM '/Users/Desktop/Database/NBAPostGres/DataOriginal/coaches_data.csv' DELIMITER ',' CSV;

The current table has no missings. So my questions are:

  1. What did I wrong and if using a table with missing values?

  2. How to import such table or handle such structure generally(also in respect to missing values)?

Data structure:

 coachid    year    yr_order    firstname   lastname    season_win
 HAMBLFR01  204        2          Frank     Hamblen         10
 RUSSEJO01  1946       1          John      Russell         22

I used:

 varchar     integer   integer  character    character     integer
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    First: you chose the wrong delimiter (the error message reports there are `';'` in the input, and your command specified `DELIMITER ','` And, since it cannot find a `,` delimiter on the line, the copy command assumes the whole line consists of one field, and chokes when not finding the second (year) column. – wildplasser Aug 31 '14 at 15:27
  • THX. I adjusted it but get ERROR: invalid input syntax for integer: "year" CONTEXT: COPY coaches, line 1, column year: "year". Is it possible that I have to account for the header somehow?(year,coachid are no integer for obvious reasons, so either I change the data type or imply the header in the command. Does it make sense?) –  Aug 31 '14 at 15:29
  • You *do* have to account for the header - or remove the line from the source. – Erwin Brandstetter Aug 31 '14 at 15:47
  • yes i got it thx you guys :---).....could someone tell me a bit about how missing values are generally treated in this context. Do I have account somehow for them before import data with missing values? –  Aug 31 '14 at 15:51

2 Answers2

31

You can have columns missing for the whole table. Tell COPY (or the psql wrapper \copy) to only fill selected columns by appending a column list to the table:

\copy coaches (coachid, yr_order, firstname)
FROM '/Users/.../coaches_data.csv' (FORMAT csv, HEADER, DELIMITER ',');

Missing values are filled in with column defaults. The manual:

If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns.

But you cannot have values missing for just some rows. That's not possible. The text representation of NULL can be used (overruling respective column defaults).

It's all in the manual, really:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

ERROR: missing data for column "year" CONTEXT: COPY coaches, line 1: ""coachid";"year";"yr_order";"firstname";"lastname";"season_win";"season_loss";"playoff_win";"playoff..."

This type of error is also the result of a Table-mismatch. The table you are importing the text file into either has more columns or less columns than the text file has.

Timal Mangra
  • 11
  • 1