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.

With pg-promise you could simply use csv filter, and it would work ;) — vitaly-t
– vitaly-t, Commented Sep 4, 2019 at 3:40
oh, that is beautiful. If you want to move your comment to an answer, I will check it as correct. — jehillert
– jehillert, Commented Sep 4, 2019 at 20:45

jehillert · Accepted Answer · 2019-09-06 16:26:18Z

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] */

edited Sep 6, 2019 at 16:26

jehillert

4621 gold badge6 silver badges16 bronze badges

answered Sep 5, 2019 at 10:59

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

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

Comments

jehillert · Accepted Answer · 2019-09-05 06:10:26Z

0

UPDATE:

Using vitaly-t's suggestion, the following code does exactly what I wanted it to do:

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

...

db.query('                             \
  INSERT INTO numeric_values_list(val) \
  SELECT * FROM UNNEST(ARRAY[$1:csv])  \
', arr]);

ORIGINAL POST:

Not sure if this solution is ideal, but the following query produced the desired result:

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

const strList = String(arr);
const delimiter = ',';

...

db.query('                                                 \
  INSERT INTO numeric_values_list(val)                     \
  SELECT * FROM UNNEST(STRING_TO_ARRAY($1, $2)::numeric[]) \
', [strList, delimiter]);

edited Sep 5, 2019 at 6:10

answered Sep 3, 2019 at 15:32

jehillert

4621 gold badge6 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related