CODEWITHSUNDEEP
postgresqlnode-postgres
d36williams
d36williams

Reputation: 449

How to Insert with a Select using Node-Postgres

In SQL and using postgressql this is a valid query, embedding a SELECT inside an INSERT -

INSERT INTO minute_registers_main_values (registers_id, t, v, i, w, pf, f) 
(
  SELECT id AS registers_id, '2015-09-01T16:34:02', 1.0, 9.1, 5.4, 1.3, 60.01 
  FROM registers WHERE main=TRUE AND cnt=0 AND hub_serial='ni1uiv'
);

I can insert a Foreign Key by doing a Select Lookup on the Insert without having to look up that other ID first.

In node-postgres, in order to INSERT many queries at once, I've turned to pg-format.

  const register_categoriesInsert = `
      INSERT INTO register_categories (register_id, category_id) VALUES %L
    `;

    await client.query(format(register_categoriesInsert, solar_ids.concat(main_ids).concat(all_other_ids)),[], (err, result)=>{
      console.log(err);
      console.log(result);
    });

This allows you to insert many values at once off of one query call. Though I have my questions about pg-format -- it doesn't seem to use parameterization.

I'm trying to do both a large number of inserts and to take advantage of using SELECTS within an INSERT.

Can I do this using node-postgres?

Upvotes: 0

Views: 1409

Answers (3)

lentyai
lentyai

Reputation: 690

It's called Parameterized Query

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', '[email protected]']
const res = await client.query(text, values)
console.log(res.rows[0])
// { name: 'brianc', email: '[email protected]' }

Upvotes: 0

RJA
RJA

Reputation: 457

I had a similar issue, thanks for the d36williams answer, that helped me out. For anyone else having this issue. Here was my solution.

// INSERT
              const newEmailRecord = {
                text: '
                 INSERT INTO my_Table(
                   col1, 
                   col2, 
                   col3, 
                   col4, 
                   col5, 
                   col6, 
                   col7
                 ) 
                 (SELECT 
                   $1, 
                   $2, 
                   column_name_3 AS col3, 
                   column_name_4 AS col4, 
                   $3, 
                   $4, 
                   $5 
                   FROM my_other_table WHERE some_id=($6)
                  )',
                values:[ var1, var2, var3, var4, var5, idToMatch],
              }
  
              await client.query(newEmailRecord);

Upvotes: 1

d36williams
d36williams

Reputation: 449

Bergi above is right.

  INSERT INTO minute_registers_main_values (registers_id, t, v, i, w, pf, f) 
  (
    SELECT id AS registers_id, $1, $2, $3, $4, $5, $6 
    FROM registers WHERE main=$7 AND cnt=$8 AND hub_serial=$9
  );

can be sent into client.query with a multidimensional array of args

Upvotes: 1

Related Questions