X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7BCE73A62D1 for ; Mon, 13 Dec 2004 16:28:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79078-05 for ; Mon, 13 Dec 2004 16:28:41 +0000 (GMT) Received: from web51303.mail.yahoo.com (web51303.mail.yahoo.com [206.190.38.169]) by svr1.postgresql.org (Postfix) with SMTP id 8E5D23A61BA for ; Mon, 13 Dec 2004 16:28:40 +0000 (GMT) Received: (qmail 60933 invoked by uid 60001); 13 Dec 2004 16:28:39 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=sa3UjX0HvmuSD2G72swKnJertM+gamt+bWqqThQjXgwTgMsRlT/VXiQRM0EMFpdH6it44yOFOal6J1MuZBd7GhZ2Kh4C7vJnK9SfpaxKQZB+5hCieUQ5as8h+/lIeLhIlAQbWFyA8x50dnXCY1HxbLYlcO/dIqkXott5Hnqpkng= ; Message-ID: <20041213162839.60931.qmail@web51303.mail.yahoo.com> Received: from [65.218.182.242] by web51303.mail.yahoo.com via HTTP; Mon, 13 Dec 2004 08:28:39 PST Date: Mon, 13 Dec 2004 08:28:39 -0800 (PST) From: sarlav kumar Subject: INSERT question To: pgsqlperform , pgsql-novice@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-644985442-1102955319=:58520" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.378 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200412/81 X-Sequence-Number: 11658 --0-644985442-1102955319=:58520 Content-Type: text/plain; charset=us-ascii Hi All, I have a question regarding multiple inserts. The following function inserts for each country found in country table, values into merchant_buyer_country. ---------------------------------------------------------------------------------------------------------------------------------------- CSQLStatement st( sql ); CSQLStatement st1( sql ); SQLINTEGER rows; long num_codes = 0; rows = st.Select( "SELECT * FROM merchant_buyer_country where merchant_id = %lu ",merchant_id ); if ( rows ) return 0; char code[4]; rows = st.Select( "SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL" ); SQLBindCol( st.hstmt, 1, SQL_C_CHAR, code, sizeof(code), 0 ); long i; for (i = 0; i < rows; i++ ) { st.Fetch(); st1.Command("INSERT INTO merchant_buyer_country (merchant_id,country,enabled,group_id) VALUES(%lu ,'%s', true, %lu )", merchant_id, code,group_id); } st.CloseCursor(); st1.CloseCursor(); return 1; ---------------------------------------------------------------------------------------------------------------------------------------- On looking at the log file, I saw separate inserts being performed, and each insert takes about 1 second. insert into merchant_buyer_country (merchant_id,country,enabled,group_id) values(1203,'IN','true',1); insert into merchant_buyer_country merchant_id,country,enabled,group_id) values(1203,'US','true',1); insert into merchant_buyer_country merchant_id,country,enabled,group_id) values (1203,'AR','true',1); insert into merchant_buyer_country (merchant_id,country,enabled,group_id) values(1203,'AZ','true',1); insert into merchant_buyer_country merchant_id,country,enabled,group_id) values (1203,'BG','true',1); insert into merchant_buyer_country merchant_id,country,enabled,group_id) values(1203,'SP','true',1); ..... There are more than 100 countries and this takes a lot of time for the inserts to complete. Is there a way to write the INSERT as follows? INSERT into merchant_buyer_country (merchant_id,country,enabled,group_id) values (1203, (SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL), 'true',1); I tried this, but I get the following problem: ERROR: More than one tuple returned by a subselect used as an expression. I know there is a way to this, but I am not sure where I am going wrong. Can someone please help me figure this out. Thanks, Saranya --------------------------------- Do you Yahoo!? Meet the all-new My Yahoo! – Try it today! --0-644985442-1102955319=:58520 Content-Type: text/html; charset=us-ascii
Hi All,
 
I have a question regarding multiple inserts.
The following function inserts for each country found in country table, values into merchant_buyer_country.
 
----------------------------------------------------------------------------------------------------------------------------------------
        CSQLStatement st( sql );
        CSQLStatement st1( sql );
        SQLINTEGER rows;
        long num_codes = 0;
        rows = st.Select( "SELECT * FROM merchant_buyer_country where merchant_id = %lu ",merchant_id );
        if  ( rows )
                return 0;
    char code[4];
        rows = st.Select( "SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL" );
    SQLBindCol( st.hstmt, 1, SQL_C_CHAR, code, sizeof(code), 0 );
   long i;
   for (i = 0; i < rows; i++ )
   {
          st.Fetch();
          st1.Command("INSERT INTO merchant_buyer_country (merchant_id,country,enabled,group_id)  VALUES(%lu ,'%s', true, %lu )", merchant_id,
 code,group_id);
   }
        st.CloseCursor();
    st1.CloseCursor();
        return 1;
----------------------------------------------------------------------------------------------------------------------------------------
On looking at the log file, I saw separate inserts being performed, and each insert takes about 1 second.
 
insert into merchant_buyer_country (merchant_id,country,enabled,group_id) values(1203,'IN','true',1);
insert into merchant_buyer_country merchant_id,country,enabled,group_id)  values(1203,'US','true',1);
insert into merchant_buyer_country merchant_id,country,enabled,group_id) values (1203,'AR','true',1);
insert into merchant_buyer_country (merchant_id,country,enabled,group_id) values(1203,'AZ','true',1);
insert into merchant_buyer_country merchant_id,country,enabled,group_id) values (1203,'BG','true',1);
insert into merchant_buyer_country merchant_id,country,enabled,group_id) values(1203,'SP','true',1);
.....
 
There are more than 100 countries and this takes a lot of time for the inserts to complete.
Is there a way to write the INSERT as follows?
 
INSERT into merchant_buyer_country (merchant_id,country,enabled,group_id)  values (1203,
(SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL), 'true',1);
 
I tried this, but I get the following problem:
ERROR:  More than one tuple returned by a subselect used as an expression.
 
I know there is a way to this, but I am not sure where I am going wrong. Can someone please help me figure this out.
 
Thanks,
Saranya
 


Do you Yahoo!?
Meet the all-new My Yahoo! – Try it today! --0-644985442-1102955319=:58520--