PostgreSQL中COPY的语法 [英] syntax for COPY in postgresql

问题描述

INSERT INTO contacts_lists (contact_id, list_id)
          SELECT contact_id, 67544
          FROM plain_contacts

在这里，我想在SQL中使用Copy命令而不是Insert命令来减少插入值的时间。我使用选择操作获取了数据。我如何使用Postgresql中的Copy命令将其插入表中。您能举个例子吗？或任何其他建议以减少插入值的时间。

Here I want to use Copy command instead of Insert command in sql to reduce the time to insert values. I fetched the data using select operation. How can i insert it into a table using Copy command in postgresql. Could you please give an example for it?. Or any other suggestion in order to achieve the reduction of time to insert the values.

推荐答案

因为您的行已在数据库中（因为您显然可以 SELECT 进行选择），所以使用COPY不会以任何方式提高速度。

As your rows are already in the database (because you apparently can SELECT them), then using COPY will not increase the speed in any way.

要使用COPY，您必须先将值写入文本文件，然后再将其读入数据库。但是，如果可以 SELECT 进行操作，则写入文本文件是完全不必要的步骤，并且会降低插入速度，而不会提高其速度

To be able to use COPY you have to first write the values into a text file, which is then read into the database. But if you can SELECT them, writing to a textfile is a completely unnecessary step and will slow down your insert, not increase its speed

您的语句速度很快。唯一可以加快速度的方法（除了购买更快的硬盘外）是删除 contact_lists 上包含列 contact_id 的任何潜在索引code>或 list_id ，并在插入完成后重新创建索引。

Your statement is as fast as it gets. The only thing that might speed it up (apart from buying a faster harddisk) is to remove any potential index on contact_lists that contains the column contact_id or list_id and re-create the index once the insert is finished.

这篇关于PostgreSQL中COPY的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！