How to do insert with array in SQL with JavaScript Node?

Question

i have a problem. I have an array and i want that each element to be a row from column;

Example:

My Array: const myArray = ['A', 'B', 'C', 'D'];

and result is:

Column1 | 
-----------------------------------------
    A   |  
    B   |  
    C   |  
    D   |

I am using Node.js with library node-postgres:

var queryString = "INSERT INTO test (id) VALUES (" + "'" + [myArray] + "'" + ") RETURNING *";

pool.connect((err, client, done) => {
  if (err) {
    done();
    console.log(err)
    return res.status(500).json({
      success: false,
      data: err
    })
  }
  client.query(queryString, (err, result) => {
    done();
  });
})

But it goes like a bigger string;

Someone could help me?

vitaly-t · Accepted Answer · 2019-03-27 08:05:00Z

2

I think this is what you're looking for:

var queryString = `INSERT INTO test (id) VALUES ("${myArray.join('"),("')}") RETURNING *`

i.e.

INSERT INTO test (id) VALUES (("A"),("B"),("C"),("D")) RETURNING *

A better solution would be to use query parameters:

var queryString = `INSERT INTO test (id) VALUES (${myArray.map((v,i) => `$${i+1}`).join('),(')}) RETURNING *`

i.e.

INSERT INTO test (id) VALUES (($1),($2),($3),($4)) RETURNING *

And then use it like:

client.query(queryString, myArray, (err, result) => {
  done();
});

edited Mar 27, 2019 at 8:05

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

answered Dec 15, 2018 at 0:43

ic3b3rg

15k4 gold badges33 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ELD Over a year ago

Hello, same thing still :(

ic3b3rg Over a year ago

@muistooshort Yes, you're right - query parameters would be a better solution - I updated my answer

mu is too short Over a year ago

Or ${myArray.map((v,i) => `($${i+1})`).join(',')} if ('),(') looks like it is staring at you ;)

vitaly-t Over a year ago

I have corrected it to what the correct SQL should contain. The insert pairs all should be additionally wrapped by () for the whole set.

ALMEK · Accepted Answer · 2018-12-25 06:47:26Z

0

Well! I am sure this should have been asked somewhere. but whatever.. ..you should have a code look like the following. - by simply add multiple inserted values as nested array. :

    var queryString = "INSERT INTO test (id) VALUES ($1) RETURNING *";
    var values = [['A'],['B'],['C'],['D']]

and when you come to the client query

   client.query(queryString, values, (err, result) => ...

edited Dec 25, 2018 at 6:47

answered Dec 25, 2018 at 6:40

ALMEK

1941 gold badge3 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to do insert with array in SQL with JavaScript Node?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related