postgresql 中 COPY 的语法
INSERT INTO contacts_lists (contact_id, list_id)
SELECT contact_id, 67544
FROM plain_contacts
这里我想在sql中使用Copy命令代替Insert命令来减少插入值的时间。我使用选择操作获取数据。如何使用 postgresql 中的 Copy 命令将其插入表中？您能举个例子吗？或任何其他建议，以减少插入值的时间。
INSERT INTO contacts_lists (contact_id, list_id)
SELECT contact_id, 67544
FROM plain_contacts
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
由于您的行已经在数据库中（因为您显然可以
SELECT它们），因此使用 COPY 不会以任何方式提高速度。为了能够使用 COPY，您必须首先将值写入文本文件，然后将其读入数据库。但是，如果您可以
SELECT它们，则写入文本文件是完全不必要的步骤，并且会减慢插入速度，而不是提高其速度您的语句已经达到了最快的速度。唯一可能加快速度的方法（除了购买更快的硬盘）是删除
contact_lists上包含列contact_id或list_id的任何潜在索引code> 并在插入完成后重新创建索引。As your rows are already in the database (because you apparently can
SELECTthem), then using COPY will not increase the speed in any way.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
SELECTthem, writing to a textfile is a completely unnecessary step and will slow down your insert, not increase its speedYour 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_liststhat contains the columncontact_idorlist_idand re-create the index once the insert is finished.我确信您可以在很多地方找到描述的语法。其中之一是这篇 wiki 文章。
看起来基本上是：
COPY plain_contacts (contact_id, 67544) TO some_file和
COPY contact_lists (contact_id, list_id) FROM some_file但我只是从资源中读取谷歌出现了。如果您需要解决特定问题的帮助，请尝试并回复。
You can find the syntax described in many places, I'm sure. One of those is this wiki article.
It looks like it would basically be:
COPY plain_contacts (contact_id, 67544) TO some_fileAnd
COPY contacts_lists (contact_id, list_id) FROM some_fileBut I'm just reading from the resources that Google turned up. Give it a try and post back if you need help with a specific problem.