Re: Insert more than one t-uple in a single sql

There is a little trick you can do though, it goes something like this:

insert into table (field1, field2, field3) select v1, v2, v3 union
b1, b2, b3 union select c1, c2, c3

I originally did this because it was significantly faster on SQL
Server 2000 than doing the inserts individually.  Usually I did it
with up to maybe 20 rows at a time that were all grouped to some kind
of common parent.

Some version of postgres a long time ago broke my code because it did
some stricter type checking and so I had to make sure that I was
never putting single quotes around int and that date fields were
strictly typecasted so that it wouldn't think they were strings.  It
does work now though as long as I do that and I use it all the time.
I don't know if it gets the same sort of speed boost in postgres as
it did in sql server.  As long as they are all done within a single
transaction in postgres it may not matter whether you do them
individually or batched like that.

If you really have a lot of data you want to insert at once why not
just use COPY?

Rick


On Feb 9, 2006, at 4:13 PM, Chris wrote:

> Hi,
>
> You can't do that in postgres, sorry. That's a mysql-ism.
>
> Gonzalo Villegas wrote:
>
>> It must be something like
>> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
>> (c1,c2,...)
>
> ---------------------------(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
>