How to import tables with missing values?

问题:

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

回答1:

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

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

Missing values are filled in with column defaults. Per documentation:

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:

• SQL-COPY
• psql \copy

回答2:

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.