如何从PostgreSQL数据库中的文本文件加载数据?

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表中。

4 个解决方案

#1

The slightly modified version of COPY below worked better for me, where I specify the CSV format. This format treats backslash characters in text without any fuss. The default format is the somewhat quirky TEXT.

下面稍微修改的版本对我来说更好，我在这里指定了CSV格式。这种格式处理文本中的反斜杠字符没有任何麻烦。默认格式是有点奇怪的文本。

COPY myTable FROM '/path/to/file/on/server' ( FORMAT CSV, DELIMITER('|') );

#2

Let consider that your data are in the file values.txt and that you want to import them in the database table myTable then the following query does the job

假设您的数据位于文件值中。txt和您想要将它们导入到数据库表myTable中，然后执行以下查询。

COPY myTable FROM 'value.txt' (DELIMITER('|'));

https://www.postgresql.org/docs/current/static/sql-copy.html

#3

Check out the COPY command of Postgres:

查看Postgres的拷贝命令:

http://www.postgresql.org/docs/current/static/sql-copy.html

#4

There's Pgloader that uses the aforementioned COPY command 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)  
  …

#1