Node-postgres: How to write insert update query in Node Js postgres

[dipakdas99]

I have an array of objects data:
assignmentData

[
  { task_id: 1, emp_id: 1234, status: true },
  { task_id: 2, emp_id: 1234, status: false },
  { task_id: 3, emp_id: 1234, status: false },
  { task_id: 4, emp_id: 1234, status: false },
  { task_id: 5, emp_id: 1234, status: true }
]

I wanted to insert into table and my query is look like:

const loadAssignmentStatus = {
   name: 'insert-assignment-records',
   text: 'INSERT INTO ramp_task_status'
        + ' (task_id, emp_id, status_id)'
        + ' VALUES(?) ON CONFLICT (task_id, emp_id) DO UPDATE'
        + ' SET status_id = 1002',
   values: [],
};

router.post('/', async (req, res) => {
  console.log(req.body)

  const assignmentData  = req.body

  try {
    const res = await db.query(loadAssignmentStatus, [assignmentData])
    console.log(res)
  } catch (err) {
    console.log("error: ", err);

  }
});

Please help me to insert bulk data into table.

[charmander]

You can pass it as JSON:

const res = await db.query(loadAssignmentStatus, [JSON.stringify(assignmentData)]);

and use jsonb_to_recordset:

INSERT INTO ramp_task_status (task_id, emp_id, status_id)
    SELECT task_id, emp_id, status
        FROM jsonb_to_recordset($1) AS t (task_id int, emp_id int, status boolean)
    ON CONFLICT (task_id, emp_id) DO UPDATE SET status_id = 1002

[dipakdas99]

Hi @charmander

If I wanted to update status as well, how can I write?

const loadAssignmentStatus = {
   name: 'insert-assignment-records',
   text: 'INSERT INTO public.ntnx_ramp_task_status (task_id, emp_id, status_id)'
      + ' SELECT task_id, emp_id, status'
      + ' FROM jsonb_to_recordset($1) AS t (task_id int, emp_id int, status int)'
      + ' ON CONFLICT (task_id, emp_id) DO UPDATE SET status_id = SELECT status FROM jsonb_to_recordset($2) AS a (status int)',
   values: [],
};

router.post('/', async (req, res) => {
  console.log(req.body)

  const assignmentData  = req.body

  try {
    const {rowCount} = await db.query(loadAssignmentStatus, [JSON.stringify(assignmentData), JSON.stringify(assignmentData.status)])
    console.log(rowCount)
    res.send(rowCount)
  } catch (err) {
    console.log("error: ", err);

  }
});

Is the above approach is right? Sorry for asking the silly question.

[charmander]

See excluded:

INSERT INTO ramp_task_status (task_id, emp_id, status_id)
    SELECT task_id, emp_id, status
        FROM jsonb_to_recordset($1) AS t (task_id int, emp_id int, status boolean)
    ON CONFLICT (task_id, emp_id) DO UPDATE SET status_id = excluded.status_id