Search code examples
postgresqlpostgresql-copy

syntax for COPY in postgresql

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.

Solution

  • 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.

    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

    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.