Syntax for using UNNEST in node-postgres query-string to insert array as multiple rows

Question

I am trying it insert a simple array of numbers as different rows of a PostgreSQL table.

For example, I want to insert:

const arr = [1.1, 2.0, 3.40, 4.5, -5];

so that table 'numeric_values_list' populates to:

id | val 
--------
1  | 1.1
2  | 2.0
3  | 3.40
4  | 4.5
5  | -5.6

Using the psql terminal, I can get this query to produce the desired result:

INSERT INTO numeric_values_list(val)
SELECT * FROM UNNEST (ARRAY[1.1, 2.0, 3.40, 4.5, -5]);

However, I cannot figure out the proper syntax for the postgres-pg query string that will produce the same result. The following has not worked for me:

  const list = [1.1, 2.0, 3.40, 4.5, -5];

  // version 1
  db.query('INSERT INTO numeric_values_list(val) SELECT * FROM UNNEST($1)', list)

  // version 2
  db.query('INSERT INTO numeric_values_list(val) SELECT * FROM UNNEST($1::numeric[])', list)

  // version 3
  db.query('INSERT INTO numeric_values_list(val) SELECT * FROM UNNEST($1::[])', list)

  // version 4
  db.query('INSERT INTO numeric_values_list(val) SELECT * FROM UNNEST ARRAY[($1)]', list)

My question is how do I get this to work using the pg-promise library?
Thanks much.

Answer 1

This can be much simpler when using pg-promise:

const arr = [1.1, 2.0, 3.40, 4.5, -5];

db.none('INSERT INTO numeric_values_list(val) SELECT * FROM UNNEST(ARRAY[$1:csv])', [arr])
  .catch(error => {
      /* when in error */
  });

See CSV Filter.

And if your input is always a non-empty array, then it can be further simplified to just this:

db.none('INSERT INTO numeric_values_list(val) SELECT * FROM UNNEST($1)', [arr])
  .catch(error => {
      /* when in error */
  });

That's because by default a non-empty array is formatted by pg-promise as array[...].

Check this out:

const pgp = require('pg-promise')(/* init options */);

const s = pgp.as.format('$1', [arr]);

console.log(s); /*=> array[1.1,2,3.4,4.5,-5] */