你可以使用COPY表TO…使用二进制，它“比文本和CSV格式略快”。只有当您有数百万行要插入，并且您对二进制数据感到满意时才这样做。

下面是一个使用psycopg2和二进制输入的Python食谱示例。

我用本地libpq方法实现了非常快速的Postgresq数据加载器。 试试我的套餐https://www.nuget.org/packages/NpgsqlBulkCopy/

使用COPY还有一种替代方法，即Postgres支持的多行值语法。从文档中可以看到:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

上面的代码插入了两行，但是您可以任意扩展它，直到达到预处理语句令牌的最大数量(可能是999美元，但我不能100%确定)。有时不能使用COPY，对于这些情况，这是一个有价值的替代品。

正如其他人所注意到的，在将数据导入Postgres时，会因为Postgres为您设计的检查而减慢速度。此外，您经常需要以某种方式操作数据，以使其适合使用。任何可以在Postgres进程之外完成的操作都意味着您可以使用COPY协议进行导入。

For my use I regularly import data from the httparchive.org project using pgloader. As the source files are created by MySQL you need to be able to handle some MySQL oddities such as the use of \N for an empty value and along with encoding problems. The files are also so large that, at least on my machine, using FDW runs out of memory. pgloader makes it easy to create a pipeline that lets you select the fields you want, cast to the relevant data types and any additional work before it goes into your main database so that index updates, etc. are minimal.

May be I'm late already. But, there is a Java library called pgbulkinsert by Bytefish. Me and my team were able to bulk insert 1 Million records in 15 seconds. Of course, there were some other operations that we performed like, reading 1M+ records from a file sitting on Minio, do couple of processing on the top of 1M+ records, filter down records if duplicates, and then finally insert 1M records into the Postgres Database. And all these processes were completed within 15 seconds. I don't remember exactly how much time it took to do the DB operation, but I think it was around less then 5 seconds. Find more details from https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html

你可以使用COPY表TO…使用二进制，它“比文本和CSV格式略快”。只有当您有数百万行要插入，并且您对二进制数据感到满意时才这样做。

下面是一个使用psycopg2和二进制输入的Python食谱示例。