How to Insert with a Select using Node-Postgres

Question

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?

Sure you can, just pass the SQL from your first snippet to client.query. — Bergi
– Bergi, Commented May 20, 2022 at 1:45

d36williams · Accepted Answer · 2022-05-20 02:21:02Z

1

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

edited May 20, 2022 at 2:21

answered May 20, 2022 at 2:12

d36williams

4472 silver badges17 bronze badges

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

Comments

RJA · Accepted Answer · 2023-09-11 14:04:22Z

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);

lentyai · Accepted Answer · 2024-08-23 23:26:55Z

0

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]' }

answered Aug 23, 2024 at 23:26

lentyai

7001 gold badge13 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to Insert with a Select using Node-Postgres

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related