postgresqlbulk-load

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
Solution

  • 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: