如何导入缺少值的表？ [英] How to import tables with missing values?

问题描述

我使用篮球数据表对Postgres 9.2& phppgadmin。因此，我想将csv表导入该数据库。但是，我得到：

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..."

使用命令：

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

当前表没有丢失，所以我的问题是：

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

1. 我错了什么，并且如果使用的表缺少值？

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

如何一般导入此类表或处理此类结构（还涉及缺失值）？

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

数据结构：

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

我用过：

 varchar     integer   integer  character    character     integer

推荐答案

整个表中可能缺少列。告诉 COPY （或psql包装器 \copy ）仅填充那些列该表的列列表，例如：

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:

如果表中的任何列均不在列列表中，则
COPY FROM 将为这些列插入默认值。

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.

但是您不能缺少一些行的值。那是不可能的。可以使用 NULL 的文本表示形式（排除各个列的默认值）。

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).

：

• SQL- COPY


• psql \复制

• SQL-COPY
• psql \copy

这篇关于如何导入缺少值的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！