Thread: multiple row insertion

multiple row insertion

From

"test tester"

Date:

04 October 2007, 11:20:08

In MySQL, I can insert multiple rows like this:


insert into cars values(5, "toyota"),(5,"ford"), etc.


How can I do something similiar in PostgreSQL?

Re: multiple row insertion

From

"Mikko Partio"

Date:

04 October 2007, 11:34:53

On 10/4/07, test tester <test896@gmail.com> wrote:

In MySQL, I can insert multiple rows like this:
insert into cars values(5, "toyota"),(5,"ford"), etc.


How can I do something similiar in PostgreSQL?

Exactly the same way. Make sure though that your pgsql is new enough version (8.2 ?).

Regards

MP

Re: multiple row insertion

From

"Ashish Karalkar"

Date:

04 October 2007, 11:42:37

INSERT INTO qsweb.core_board(board_name, entry_user_id, entry_date)
VALUES ('a',1,now()),('b',1,now());

----- Original Message -----
From: test tester
To: pgsql-general@postgresql.org
Sent: Thursday, October 04, 2007 4:49 PM
Subject: [GENERAL] multiple row insertion

In MySQL, I can insert multiple rows like this:
insert into cars values(5, "toyota"),(5,"ford"), etc.

How can I do something similiar in PostgreSQL?
insert into cars (id,name) values (1,'toyota'),(2,'ford');

With Regards
Ashish

Re: multiple row insertion

From

"test tester"

Date:

04 October 2007, 13:47:14

On 10/4/07, test tester <test896@gmail.com> wrote:

i have version 8.1 and i want to know how to insert multiple rows in this version.
On 10/4/07, Ashish Karalkar < ashish.karalkar@info-spectrum.com> wrote:
INSERT INTO qsweb.core_board(board_name, entry_user_id, entry_date)
VALUES ('a',1,now()),('b',1,now());
----- Original Message -----
From: test tester
To: pgsql-general@postgresql.org
Sent: Thursday, October 04, 2007 4:49 PM
Subject: [GENERAL] multiple row insertion

In MySQL, I can insert multiple rows like this:

insert into cars values(5, "toyota"),(5,"ford"), etc.

How can I do something similiar in PostgreSQL?

insert into cars (id,name) values (1,'toyota'),(2,'ford');

With Regards
Ashish

Re: multiple row insertion

From

"Dawid Kuroczko"

Date:

04 October 2007, 14:03:14

> On 10/4/07, test tester <test896@gmail.com> wrote:
> > i have version 8.1 and i want to know how to insert multiple rows in this
> version.

Please don't top post.

If you need this functionality, you should really upgrade.

In cases where you want to insert multiple rows in version 8.1, you
could use COPY command:
   http://www.postgresql.org/docs/8.1/interactive/sql-copy.html

You use it like this:
COPY cars FROM STDIN DELIMITER AS ',' CSV;
5,toyota
6,ford
\.

Or if you really need to use INSERT you could use such construct:

INSERT INTO cars SELECT 5, 'toyota' UNION ALL SELECT 6, 'ford'
  UNION ALL SELECT 7, 'bmw';

In short: really get the 8.2 version.  8.2 is compatible with earlier
versions and will be coming soon.

  Regards,
     Dawid
--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?

Re: multiple row insertion

From

"A. Kretschmer"

Date:

04 October 2007, 14:15:24

am  Thu, dem 04.10.2007, um 18:47:01 +0500 mailte test tester folgendes:
>
>
> On 10/4/07, test tester <test896@gmail.com> wrote:
>
>     i have version 8.1 and i want to know how to insert multiple rows in this
>     version.

Please no silly top post.

You can insert multiple values with one insert with multiple select and
UNION like this example:

test=*# truncate foo;
TRUNCATE TABLE
test=*# select * from foo;
 w
---
(0 rows)

test=*# insert into foo  select 'foo1' union select 'foo2' union select 'foo3';
INSERT 0 3
test=*# select * from foo;
  w
------
 foo1
 foo2
 foo3
(3 rows)



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: multiple row insertion

From

Alvaro Herrera

Date:

04 October 2007, 14:23:21

test tester escribió:
> On 10/4/07, test tester <test896@gmail.com> wrote:
>
> > i have version 8.1 and i want to know how to insert multiple rows in this
> > version.

Upgrade.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: multiple row insertion

From

André Volpato

Date:

04 October 2007, 14:56:49

Alvaro Herrera escreveu: <blockquote cite="mid:20071004142301.GI6176@alvh.no-ip.org" type="cite"><pre wrap="">test
testerescribió: </pre><blockquote type="cite"><pre wrap="">On 10/4/07, test tester <a class="moz-txt-link-rfc2396E"
href="mailto:test896@gmail.com"><test896@gmail.com></a>wrote: 
   </pre><blockquote type="cite"><pre wrap="">i have version 8.1 and i want to know how to insert multiple rows in this
version.     </pre></blockquote></blockquote><pre wrap="">
Upgrade.
 </pre></blockquote> Use COPY instead.<br /><br /> Put you data into a var, and perform a COPY from STDIN.<br /><br />
[]´s<br/> ACV<br />