database 如何从 PostgreSQL 数据库中的文本文件加载数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5233321/
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 load data from a text file in a PostgreSQL database?
提问by ulima69
I have a file like (CSV file):
我有一个像（CSV 文件）这样的文件：
value1|value2|value2....
value1|value2|value2....
value1|value2|value2....
value1|value2|value2....
and would like to load these data into a postgresql table.
并希望将这些数据加载到 postgresql 表中。
采纳答案by ulima69
The slightly modified version of COPYbelow worked better for me, where I specify the CSVformat. This format treats backslash characters in text without any fuss. The default format is the somewhat quirky TEXT.
下面稍微修改的版本COPY对我来说效果更好，我在其中指定了CSV格式。这种格式可以毫不费力地处理文本中的反斜杠字符。默认格式是有点古怪的TEXT.
COPY myTable FROM '/path/to/file/on/server' ( FORMAT CSV, DELIMITER('|') );
回答by Fopa Léon Constantin
Let consider that your data are in the file values.txtand that you want to import them in the database table myTablethen the following query does the job
让我们考虑您的数据在文件中values.txt，并且您想将它们导入数据库表中，myTable然后以下查询完成工作
COPY myTable FROM 'value.txt' (DELIMITER('|'));
https://www.postgresql.org/docs/current/static/sql-copy.html
https://www.postgresql.org/docs/current/static/sql-copy.html
回答by a_horse_with_no_name
Check out the COPY command of Postgres:
查看Postgres的COPY命令：
回答by Ehvince
There's Pgloaderthat uses the aforementioned COPYcommand and which can load data from csv (and MySQL, SQLite and dBase). It's also using separate threads for reading and copying data, so it's quite fast (interestingly enough, it got written from Python to Common Lisp and got a 20 to 30x speed gain, see blog post).
有使用上述命令的Pgloader，COPY它可以从 csv（以及 MySQL、SQLite 和 dBase）加载数据。它还使用单独的线程来读取和复制数据，因此速度非常快（有趣的是，它是从 Python 编写到 Common Lisp 并获得了 20 到 30 倍的速度增益，请参阅博客文章）。
To load the csv file one needs to write a little configuration file, like
要加载 csv 文件，需要编写一个小配置文件，例如
LOAD CSV
FROM 'path/to/file.csv' (x, y, a, b, c, d)
INTO postgresql:///pgloader?csv (a, b, d, c)
…
