Обсуждение: inserting multiple values in version 8.1.5
Hi
I am trying to insert multiple values into a table like this.
INSERT INTO tab_name (col1, col2) VALUES (val1, val2), (val3, val4)
This works in postgres version 8.2.1
My production server runs in 8.1.5. It gives me
ERROR: syntax error at or near "," at character 35
What to do?
thanks
I am trying to insert multiple values into a table like this.
INSERT INTO tab_name (col1, col2) VALUES (val1, val2), (val3, val4)
This works in postgres version 8.2.1
My production server runs in 8.1.5. It gives me
ERROR: syntax error at or near "," at character 35
What to do?
thanks
rkmr.em@gmail.com wrote: > Hi > I am trying to insert multiple values into a table like this. > INSERT INTO tab_name (col1, col2) VALUES (val1, val2), (val3, val4) > > This works in postgres version 8.2.1 > > My production server runs in 8.1.5. It gives me > ERROR: syntax error at or near "," at character 35 That came in at v8.2. You can't use it in 8.1.5. -- Postgresql & php tutorials http://www.designmagick.com/
I need to do like 1000 inserts periodically from a web app. Is it better to do 1000 inserts or 1 insert with the all 1000 rows? Is using copy command faster than inserts?
thanks
thanks
On 4/2/07, Chris <dmagick@gmail.com> wrote:
rkmr.em@gmail.com wrote:
> Hi
> I am trying to insert multiple values into a table like this.
> INSERT INTO tab_name (col1, col2) VALUES (val1, val2), (val3, val4)
>
> This works in postgres version 8.2.1
>
> My production server runs in 8.1.5. It gives me
> ERROR: syntax error at or near "," at character 35
That came in at v8.2.
You can't use it in 8.1.5.
--
Postgresql & php tutorials
http://www.designmagick.com/
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I am trying to insert multiple values into a table like this. > INSERT INTO tab_name (col1, col2) VALUES (val1, val2), (val3, val4) ... > My production server runs in 8.1.5. ... > What to do? Upgrade to 8.2. :) Seriously, you should upgrade to 8.1.8. You can add multiple rows in one statement like this: INSERT INTO tab_name (col1,col2) SELECT val1, val2 UNION ALL SELECT val3, val4; - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200704031025 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFGEmRGvJuQZxSWSsgRA+dyAJ9buRgJdNfSK4pOWZQT+/bxZ27yEgCeO6AJ sWpYA1cMbjHIziROLwrXwrM= =Oeqk -----END PGP SIGNATURE-----
am Tue, dem 03.04.2007, um 7:19:15 -0700 mailte rkmr.em@gmail.com folgendes: > I need to do like 1000 inserts periodically from a web app. Is it better to do > 1000 inserts or 1 insert with the all 1000 rows? Is using copy command faster > than inserts? You can do the massive Inserts within one transaktion, but COPY is much faster than many Inserts. The multi-line Insert is a new feature since 8.2. I prefer COPY. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Apr 3, 2007, at 10:33 AM, A. Kretschmer wrote: > am Tue, dem 03.04.2007, um 7:19:15 -0700 mailte rkmr.em@gmail.com > folgendes: >> I need to do like 1000 inserts periodically from a web app. Is it >> better to do >> 1000 inserts or 1 insert with the all 1000 rows? Is using copy >> command faster >> than inserts? > > You can do the massive Inserts within one transaktion, but COPY is > much > faster than many Inserts. The multi-line Insert is a new feature since > 8.2. I prefer COPY. not all database drivers support copy , so that might not be applicable. I know the perl DBD::Pg does, but I haven't seen it in many other languages. you could try doing all the inserts in 1 transaction in a loop using a prepared statement. that should give you a bit of a speedup. ie (in bastardized perl/python): $db->begin $prepared_statement= """INSERT INTO x (a,b) VALUES ( :id , :name );""" for row in update_loop: $prepared_statement->execute( row['id'] , row['name'] $db->commit // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -