Обсуждение: multiple insert into's (may be NEWBIE question)
I have a table (lets say a,text b,text) and I want to insert the data jim,jimmy and trav,travis can I do this with 1 insertinto statement instead of 2? Travis
On Tue, 5 Aug 2003, Williams, Travis L, NPONS wrote: > I have a table (lets say a,text b,text) and I want to insert the data > jim,jimmy and trav,travis can I do this with 1 insert into statement > instead of 2? Not with the current implementation of insert. There's been some discussion of adding the multiple tuple insert seen in other databases, but I don't think anyone's actually done it or even agreed on exactly how to do it. I'm not sure if SQL 3 covers this, it seems like it hints at it, but I can't read that stuff all that well most of the time. I don't think there's a way right now though, without using some form of copy.
Thanks.. Travis -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Tuesday, August 05, 2003 2:16 PM To: Williams, Travis L Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] multiple insert into's (may be NEWBIE question) On Tue, 5 Aug 2003, Williams, Travis L, NPONS wrote: > I have a table (lets say a,text b,text) and I want to insert the data > jim,jimmy and trav,travis can I do this with 1 insert into statement > instead of 2? Not with the current implementation of insert. There's been some discussion of adding the multiple tuple insert seen in other databases, but I don't think anyone's actually done it or even agreed on exactly how to do it. I'm not sure if SQL 3 covers this, it seems like it hints at it, but I can't read that stuff all that well most of the time. I don't think there's a way right now though, without using some form of copy.
On Tue, 5 Aug 2003, scott.marlowe wrote: > On Tue, 5 Aug 2003, Williams, Travis L, NPONS wrote: > > > I have a table (lets say a,text b,text) and I want to insert the data > > jim,jimmy and trav,travis can I do this with 1 insert into statement > > instead of 2? > > Not with the current implementation of insert. There's been some > discussion of adding the multiple tuple insert seen in other databases, > but I don't think anyone's actually done it or even agreed on exactly how > to do it. I'm not sure if SQL 3 covers this, it seems like it hints at > it, but I can't read that stuff all that well most of the time. > > I don't think there's a way right now though, without using some form of > copy. Well, you can do it with insert ... select and union. insert into tab select 'jim', 'jimmy' union select 'trav', 'travis';
On Tue, 2003-08-05 at 14:42, Stephan Szabo wrote: > On Tue, 5 Aug 2003, scott.marlowe wrote: > > > On Tue, 5 Aug 2003, Williams, Travis L, NPONS wrote: > > > > > I have a table (lets say a,text b,text) and I want to insert the data > > > jim,jimmy and trav,travis can I do this with 1 insert into statement > > > instead of 2? > > > > Not with the current implementation of insert. There's been some > > discussion of adding the multiple tuple insert seen in other databases, > > but I don't think anyone's actually done it or even agreed on exactly how > > to do it. I'm not sure if SQL 3 covers this, it seems like it hints at > > it, but I can't read that stuff all that well most of the time. > > > > I don't think there's a way right now though, without using some form of > > copy. > > Well, you can do it with insert ... select and union. > > insert into tab > select 'jim', 'jimmy' > union > select 'trav', 'travis'; But the bottom line question is "why do it it in the 1st place?". Multiple INSERT commands works like a peach, as does COPY from stdin (thanks again, Jason). -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
Didn't know if it would be any faster..
Travis
-----Original Message-----
From: Ron Johnson [mailto:ron.l.johnson@cox.net]
Sent: Tuesday, August 05, 2003 3:00 PM
To: PgSQL General ML
Subject: Re: [GENERAL] multiple insert into's (may be NEWBIE question)
On Tue, 2003-08-05 at 14:42, Stephan Szabo wrote:
> On Tue, 5 Aug 2003, scott.marlowe wrote:
>
> > On Tue, 5 Aug 2003, Williams, Travis L, NPONS wrote:
> >
> > > I have a table (lets say a,text b,text) and I want to insert the
data
> > > jim,jimmy and trav,travis can I do this with 1 insert into
statement
> > > instead of 2?
> >
> > Not with the current implementation of insert. There's been some
> > discussion of adding the multiple tuple insert seen in other
databases,
> > but I don't think anyone's actually done it or even agreed on
exactly how
> > to do it. I'm not sure if SQL 3 covers this, it seems like it hints
at
> > it, but I can't read that stuff all that well most of the time.
> >
> > I don't think there's a way right now though, without using some
form of
> > copy.
>
> Well, you can do it with insert ... select and union.
>
> insert into tab
> select 'jim', 'jimmy'
> union
> select 'trav', 'travis';
But the bottom line question is "why do it it in the 1st place?".
Multiple INSERT commands works like a peach, as does COPY from stdin
(thanks again, Jason).
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On 5 Aug 2003, Ron Johnson wrote: > On Tue, 2003-08-05 at 14:42, Stephan Szabo wrote: > > On Tue, 5 Aug 2003, scott.marlowe wrote: > > > > > On Tue, 5 Aug 2003, Williams, Travis L, NPONS wrote: > > > > > > > I have a table (lets say a,text b,text) and I want to insert the data > > > > jim,jimmy and trav,travis can I do this with 1 insert into statement > > > > instead of 2? > > > > > > Not with the current implementation of insert. There's been some > > > discussion of adding the multiple tuple insert seen in other databases, > > > but I don't think anyone's actually done it or even agreed on exactly how > > > to do it. I'm not sure if SQL 3 covers this, it seems like it hints at > > > it, but I can't read that stuff all that well most of the time. > > > > > > I don't think there's a way right now though, without using some form of > > > copy. > > > > Well, you can do it with insert ... select and union. > > > > insert into tab > > select 'jim', 'jimmy' > > union > > select 'trav', 'travis'; > > But the bottom line question is "why do it it in the 1st place?". > Multiple INSERT commands works like a peach, as does COPY from stdin > (thanks again, Jason). Some constraints might make such things meaningful when compared to multiple inserts (for example, immediate checked self-referential foreign keys where you might want to insert a row and the row it depends on in a single statement). Copy should work, but that's fairly different than inserts (if only because inserts are likely to work on other systems).