2

In the docs on https://www.postgresql.org/docs/8.2/static/sql-insert.html it states:

To insert multiple rows using the multirow VALUES syntax:

INSERT INTO films (code, title, did, date_prod, kind) 
VALUES ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
       ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

So I imagine, a parametrised query would look like:

INSERT INTO films (code, title, did, date_prod, kind)  
VALUES ($1, $2, $3, $4, $5'),
       ($6, $7, $8, $9, $10);

The above query is meant to insert two rows. But what if I want to insert a variable number of rows, or I don't know the number of rows until run time. Is there a better way to write this statement?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rahul Iyer
  • 19,924
  • 21
  • 96
  • 190
  • The simplest way is to insert them one at a time. You can wrap them in a transaction so all are effectively inserted at the same time. If you have a lot of rows, then put them into a file and load the file. – Gordon Linoff Oct 10 '17 at 11:44
  • multirow insert is slightly faster then single row, but if you have maaany rows to insert, using INSERT as prepared statement looses sence - maybe construct copy then? it does not work with PREPARE, but I guess is save to load much data from file – Vao Tsun Oct 10 '17 at 11:49
  • I think it should be closed as a duplicate of [Postgresql JDBC Table Valued Parameters](https://stackoverflow.com/questions/23008966/postgresql-jdbc-table-valued-parameters) or [PostgreSQL equivalent to SQL Server's TVP](https://stackoverflow.com/questions/35273060/postgresql-equivalent-to-sql-servers-tvp) – Vladimir Baranov Oct 10 '17 at 12:23

0 Answers0