database 将 `tsv` 文件插入 postgresql 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20455378/
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
Insert `tsv` files into postgresql db
提问by Anna.Klee
I have several files which are saved as tsv. I want to insert them into a postgresqldb, to analyze them with sql.
我有几个文件保存为 tsv。我想将它们插入到postgresql数据库中，用 sql 分析它们。
However, my problem is how to INSERTthis tsv files into postgresql 9.2under windows 7?
但是，我的问题是如何将INSERT这个 tsv 文件转换为postgresql 9.2下windows 7？
I appreciate your reply!
我很感激你的回复！
PS.: I have created the table with the right values like:
PS。：我已经用正确的值创建了表格，例如：
CREATE TABLE ratings (distribution VARCHAR, votes VARCHAR, rank FLOAT, title VARCHAR);
CREATE TABLE ratings (distribution VARCHAR, votes VARCHAR, rank FLOAT, title VARCHAR);
the file is in the directory:
该文件在目录中：
C:/Users/testUser/Desktop/TSV/ratings.list.tsv
C:/Users/testUser/Desktop/TSV/ratings.list.tsv
采纳答案by Denis de Bernardy
For tab separated values, you can use COPY:
对于制表符分隔值，您可以使用 COPY：
http://www.postgresql.org/docs/current/static/sql-copy.html
http://www.postgresql.org/docs/current/static/sql-copy.html
Depending on the exact format of your file, it could be something like:
根据文件的确切格式，它可能类似于：
COPY ratings FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv' DELIMITER '\t'
回答by Ryan Atallah
You want something like this:
你想要这样的东西：
COPY ratings FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv' DELIMITER E'\t';
The E'\t'is required, otherwise you'll get an error like this:
将E'\t'是必需的，否则你会得到这样的错误：
ERROR: DELIMITER for COPY must be a single one-byte character
错误：COPY 的分隔符必须是单个一字节字符
If the columns in your TSV don't line up perfectly with your table, you can also define the mapping by doing the following:
如果您的 TSV 中的列与您的表不完全对齐，您还可以通过执行以下操作来定义映射：
COPY ratings (column_1, column_2, ... column_n)
FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv'
DELIMITER E'\t';
回答by philshem
回答by Joseph Lust
Sad to say, but the easiest way is to convert the TSV to a CSV. Most of the built in Postgres import utilities to do things like converting empty strings to nulls, skipping headers, etc are Only for CSV.
很遗憾，但最简单的方法是将 TSV 转换为 CSV。大多数内置 Postgres 导入实用程序来执行诸如将空字符串转换为空值、跳过标题等操作仅适用于 CSV。
See this simple 6 line Python answer on SO. I use it and then CSV loaded like normal without a problem in Postgres after trying for and hour to load a TSV.
在 SO 上查看这个简单的6 行 Python 答案。我使用它，然后在尝试加载 TSV 一个小时后，像平常一样加载 CSV，在 Postgres 中没有问题。
