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/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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=2529DF...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGEmRGvJuQZxSWSsgRA+dyAJ9buRgJdNfSK4pOWZQT+/bxZ27yEgCeO6AJ
sWpYA1cMbjHIziROLwrXwrM=
=Oeqk
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly