Re: multiple inserts

hm. well. I'm looking at a data set that can potentially get a few
thousand big. So I'll stick with the COPY command.

the trick is that I'm inserting a 1000 row 20 column table. This gets
super slow, as you can imagine, so I'm looking at creating a two tables, a
1000 row table with a single column (my unique identifiers) and a 20
column table with a single row (the default values) and then UNIONing them.

would doing a COPY be a better idea?

On Fri, 17 Jun 2005 10:25:28 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Oren Mazor" <oren(dot)mazor(at)gmail(dot)com> writes:
>> I'm wondering if it is at all possible to do a mass insert into a table
>> using only a single query?
>> Something along the lines of:
>> insert into mytable values (val1), (val2), (val3)
>
> We should have that (it's in the SQL spec) but no one's gotten around to
> it. You could fake it with
>
> insert into mytable
> select val1
> union all
> select val2
> union all
> ...
>
> But if you are thinking of really large amounts of data (like more than
> a few dozen rows), you really want to use COPY instead. Neither the
> union approach nor the still-unwritten multi-insert would be likely to
> be pleasant to use for thousands/millions of rows.
>
> regards, tom lane

--
Nanny Ogg looked under her bed in case there was a man there. Well, you
never knew your luck.
(Lords and Ladies - Terry Pratchett)