inserting multiple values in version 8.1.5

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

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