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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Rafiu
  • 4,700
  • 6
  • 25
  • 27
  • 1
    Have you Googled for examples? I can see plenty with a moment's searching. – YXD Apr 25 '11 at 11:49
  • 1
    Here is the postgresql documentation for the copy command. It includes a full definition of syntax and some examples that should help you on your way. PostgreSQL has excellent documentation, so you should reference it often while working with it, especially if you've got questions about syntax. http://www.postgresql.org/docs/8.4/static/sql-copy.html – adorablepuppy Apr 25 '11 at 12:10
  • 2
    What leads you to believe that this approach will be faster than `INSERT INTO ... SELECT`? – Isaac Truett Apr 25 '11 at 13:59

2 Answers2

2

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.

1

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_file

And

COPY contacts_lists (contact_id, list_id) FROM some_file

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

Isaac Truett
  • 8,734
  • 1
  • 29
  • 48
  • thank you for your response but i have more difficult query. which is civen below. – Rafiu Apr 25 '11 at 13:53
  • INSERT INTO contacts_lists (contact_id, list_id) SELECT contact_id, 67544 FROM plain_contacts WHERE TRUE AND is_print = TRUE AND TRUE AND (NOT EXISTS (select title_id from company_types_lists_titles where company_types_list_id = 55321) OR title_id in (select title_id from company_types_lists_titles where company_types_list_id = 55321)) AND company_type_id = 7 AND country_id IN (select country_id from countries_lists WHERE list_id = 67544) AND ((state_id IS NULL OR country_id NOT IN (231,39) OR state_id IN (SELECT state_id FROM lists_states WHERE list_id = 67544)) – Rafiu Apr 25 '11 at 13:56
  • OR zone_ids && ARRAY(SELECT zone_id FROM lists_zones WHERE list_id = 67544)) AND (NOT EXISTS (select award_id from company_types_lists_top_awards where company_types_list_id = 55321) OR top_award_ids && ARRAY(select award_id from company_types_lists_top_awards where company_types_list_id = 55321)) – Rafiu Apr 25 '11 at 13:56
  • Did you look at the documentation I linked to? For example, the part of the syntax where you can include a ` ( query ) `? – Isaac Truett Apr 25 '11 at 13:58
  • The above two comments are sql query please help to use copy instad of insert – Rafiu Apr 25 '11 at 13:59