Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – Inserting jsonb data via PostgreSQL on NodeJS

I’m a newbie in backend development and just started to build a simple table including jsonb type with PostgreSQL in node.js on Ubuntu.

CREATE TABLE testTable ( id SERIAL NOT NULL PRIMARY KEY, vStr VARCHAR(64), jVar jsonb);

Then I tried to insert a data as below.

const {Client} = require('pg');
const client = new Client({
  host: 'ip addr',
  user, database name, etc...
});
client.connect();

const strA = "test string A";
const a1 = 1;
const query = {
  text: `INSERT INTO testTable (id, vStr, jVar) VALUES (${a1}, ${strA}, '{"cnt":${a1}}')`
};
client.query(query).then(client.end()).catch( error handling... );

I used variables in query statement because I have to get values from parameters.

I ran

node insertTestTable.js

but just saw

error: syntax error at or near "string"

at /MyWorkspace/node_modules/pg/lib/client.js:526:17

at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

I searched a lot in Google but failed to find a solution. Time is choking me, that’s why I ask advices in stackoverflow because there’re great engineers here.

I expected it’s added in my database.

Column A Column B
id 1
vStr "test string A"
jVar {"cnt":1}

===========================================

@Bergi, Could you please help once more?
What I wanted to was as below, more complex case.

const strA = "test string A";
const a1 = 1;
const pr = true;

const query = {
  text: "INSERT INTO testTable (id, vStr, jVar) VALUES ($1, $2, $3, $4)",
  values: [a1, strA, JSON.stringify({pro:pr,es:[{name:strA,qr:a1}]})]
};

I ran this but got an error:

error: syntax error at end of input

at /MyWorksapce/node_modules/pg/lib/client.js:526:17

at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

I really appreciate your advice.

Tags:

2

Answers


  1. 0

    You are generating the SQL text

    INSERT INTO testTable (id, vStr, jVar) VALUES (1, test string A, '{"cnt":1}')

    which is a syntax error. You’d need to escape the value as an SQL literal, however you should rather use a parameterised query:

    const query = {
  text: "INSERT INTO testTable (id, vStr, jVar) VALUES ($1, $2, $3)",
  values: [a1, strA, JSON.stringify({cnt: a1})],
};
    Login or Signup to reply.
  2. 0

    The error you’re encountering is likely due to a syntax issue in your SQL query. When using parameterized queries with the pg library, the placeholders should be specified using $1, $2, etc., and the actual values should be passed in an array through the values property.

    Here’s how you can fix your query:

    const strA = "test string A";
const a1 = 1;
const pr = true;

const query = {
  text: "INSERT INTO testTable (id, vStr, jVar) VALUES ($1, $2, $3)",
  values: [a1, strA, JSON.stringify({pro: pr, es: [{ name: strA, qr: a1 }] })],
};

client.query(query)
  .then(() => {
    console.log("Data inserted successfully");
    client.end();
  })
  .catch((err) => {
    console.error("Error inserting data:", err);
    client.end();
  });

    Key changes:

    1. I removed the unnecessary fourth placeholder ($4) from your query since you only have three columns in the INSERT statement.
    2. The values array now contains the values for the placeholders in the correct order.

    Make sure to handle the promise returned by client.query appropriately (in this case, using .then() and .catch()). This ensures that the connection is properly closed, regardless of whether the query succeeds or fails.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search