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.

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