Thread: multiple inserts
hi all,
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)
rather than doing three discrete insert into statements.
this seems like something that should be there, but a week's worth of
googling hasnt turned anything up.
thanks
Oren
--
A voice crackles in Calvin's radio:
"Enemy fighters at two o'clock!"
"Roger. What should I do until then?"
On Fri, Jun 17, 2005 at 09:32:21AM -0400, Oren Mazor wrote: > > I'm wondering if it is at all possible to do a mass insert into a table > using only a single query? See the COPY command: http://www.postgresql.org/docs/8.0/static/sql-copy.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
am 17.06.2005, um 9:32:21 -0400 mailte Oren Mazor folgendes: > hi all, > > 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) What do you mean, multiple rows, ore one row? You can insert one row with any columns with "insert into table (col1, col2, col3) values (val1, val2, val3);", and you can do a mass-insert with "copy from ...". Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
"Oren Mazor" <oren.mazor@gmail.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
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@sss.pgh.pa.us> wrote: > "Oren Mazor" <oren.mazor@gmail.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)
Gren, i'm not sure i fully understand why you would break up the table. it seems to me that breaking the table up would a normalization thing vs a "i have a lot of inserts" thing. if you have repeating data in your 20 column table and it can be normalized out, i'd go that route. if all your data is unique or can't be normalized, i'd stick with the single table. if there is some nuance i'm missing here, i would like to learn it. --- Oren Mazor <oren.mazor@gmail.com> wrote: > 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@sss.pgh.pa.us> wrote: > > > "Oren Mazor" <oren.mazor@gmail.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) > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose > an index scan if your > joining column's datatypes do not match > __________________________________ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
On Fri, Jun 17, 2005 at 10:31:12 -0400, Oren Mazor <oren.mazor@gmail.com> wrote: > 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? If you do the inserts in one transaction this should be reasonably fast. If a program is doing the inserts more speed can be gained by using prepare. However, copy is a better way to go if you aren't transforming or checking the data as it is being inserted.