Re: multiple insert into's (may be NEWBIE question)

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
Cc: PgSQL General ML <pgsql-general(at)postgresql(dot)org>
Subject: Re: multiple insert into's (may be NEWBIE question)
Date: 2003-08-05 21:08:05
Message-ID: 20030805140550.U68645-100000@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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).

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Justin Tocci 2003-08-05 21:36:39 Re: INSERT RULE QUERY ORDER
Previous Message Richard Welty 2003-08-05 20:55:48 Re: indices and cidr/inet type