Thread: Re: Inseting multiple rows at onece.

Steve <steve@zeropps.uklinux.net> wrote in message news:<3BD0B0F3.39A9CBB3@zeropps.uklinux.net>...
> Hi
>
> I've got a table that has two columns in it one is a
> sequence and the second is
> a inet unique, and I want to add multiple rows all at once
> rather than issuing
> the insert comand two thousand times.
>
> Even when trying manually I can't get it to accept two rows
> at once, I've tried
> things like:
>
> INSERT INTO ip_list (ip_address) VALUES ('212.1.136.246',
> '212.1.156.23');
>
> and
>
> INSERT INTO ip_list (ip_address) VALUES ('212.1.136.246'),
> ('212.1.156.23');
>
> but just get parse errors.  It's fine inserting one row at a
> time.
>
> Using perl using the Pg module is there a way of adding a
> whole array where each
> element will go into a seperate row.
>
> Any help greatly received.
>
> --
> Cheers
> Steve              email mailto:steve@zeropps.uklinux.net
>
> %HAV-A-NICEDAY Error not enough coffee  0 pps.
>
> web http://www.zeropps.uklinux.net/
>
> or  http://start.at/zero-pps
>
>  12:22pm  up 124 days, 13:12,  2 users,  load average: 1.00,
> 1.00, 1.00


Well, it is possible to add lots of rows in one operation with the SQL
command "COPY" (read Postgres' documentation) but running 2000 INSERT
queries on such type of table doesn't take a long time, since you seam
not to have lots of columns in your table ip_list.

Even if you have an index (unique or not) on one of the columns of
your table it won't be really time-consuming.

In my database I have a table with 350000 rows and inserting new rows
is very fast with the insert command (altougth it would be faster with
COPY - but COPY is usually used for data transfers with text files).

Regards,
 xav.

> Well, it is possible to add lots of rows in one operation with the SQL
> command "COPY" (read Postgres' documentation) but running 2000 INSERT
> queries on such type of table doesn't take a long time, since you seam
> not to have lots of columns in your table ip_list.
>
> Even if you have an index (unique or not) on one of the columns of
> your table it won't be really time-consuming.
>
> In my database I have a table with 350000 rows and inserting new rows
> is very fast with the insert command (altougth it would be faster with
> COPY - but COPY is usually used for data transfers with text files).
>
> Regards,
>  xav.


Thanks for the info.

I avoided the COPY command at first because it appeared that
it couldn't be used to populate
partial tables eg only one column of a multi column table,
but I probably just read the
docs wrongly.  I'll have another look at the docs.

Thanks again.

--
Cheers
Steve              email mailto:steve@zeropps.uklinux.net

%HAV-A-NICEDAY Error not enough coffee  0 pps.

web http://www.zeropps.uklinux.net/

or  http://start.at/zero-pps