Обсуждение: 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

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