On Wed, 2 May 2001, Renaud Thonnart wrote:
> Hello everyone!
> I have readen in the doc of PostgreSQL that we can insert several tuples
> in a table at one time.
> But it is said that the tuples to insert must be the result of a SELECT.
>
> Could someone explain me what is the advantage of that technique ?
> Because if I have to build a temporary table before being able to
> perform multiple INSERT, I work 2 times more, isn't it ?
> Or perhaps does it exist a way to perform multiple insert without build
> a temporary table; something like :
> INSERT INTO table
> VALUES(...,...,...),
> VALUES(...,...,...);
> I really need your advice!
> Thank you all,
> Sincerelly, Renaud THONNART
Let's suppose you have the tables:
CREATE TABLE source (
id int,
f1 text );1
INSERT INTO source VALUES (1, 'foo');
INSERT INTO source VALUES (2, 'bar');
INSERT INTO source VALUES (3, 'snog');
CREATE TABLE second (
id int,
f1 text );
You can't insert several rows simultaneously into second by using INSERT
... VALUES. That is, you CANNOT:
INSERT INTO second VALUES ( 1, 'foo' ), (2, 'bar');
Some databases support this, but PostgreSQL doesn't yet.
What the INSERT piece means is that you *can* do this:
INSERT INTO second SELECT * FROM source;
... and all three records will be copied into the table "second".
HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington