postgresql 如何导入缺失值的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25593338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to import tables with missing values?
提问by Erwin Brandstetter
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:
我使用篮球数据表来了解 Postgres 9.2 和 phppgadmin。因此，我想将 csv 表导入该数据库。但是，我得到：
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:
当前表没有缺失。所以我的问题是：
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)?
如果使用缺少值的表，我做错了什么？
如何导入这样的表或一般处理这样的结构（也关于缺失值）？
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
回答by Erwin Brandstetter
You can have columns missing for the whole table. Tell COPY(or the psql wrapper \copy) to only fill thosecolumns appending a column list to the table, for instance:
您可以缺少整个表的列。告诉COPY（或 psql 包装器\copy）只填充那些将列列表附加到表的列，例如：
\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 FROMwill insert the default values for those columns.
如果表中有任何不在列列表中的列，
COPY FROM将为这些列插入默认值。
But you cannothave values missing for just somerows. That's not possible. The text representation of NULLcan be used (overruling respective column defaults).
但是您不能只缺少某些行的值。那是不可能的。NULL可以使用的文本表示（否决各自的列默认值）。
It's all in the manual, really:
说明书上都有，真的：
回答by Timal Mangra
ERROR: missing data for column "year" CONTEXT: COPY coaches, line 1: ""coachid";"year";"yr_order";"firstname";"lastname";"season_win";"season_loss";"playoff_win";"playoff..."
错误：“年”列的数据缺失 上下文：复制教练，第 1 行：“”coachid”；“year”；“yr_order”；“firstname”；“lastname”；“season_win”；“season_loss”；“playoff_win”； “挑拨...”
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.
这种类型的错误也是表不匹配的结果。与文本文件相比，您将文本文件导入到的表具有更多的列或更少的列。
