pg-promise. Query works when using raw query, but not when using helpers

Question

I get an error when I use the below code:

  var rows = [{"alex", "matos"},{"john","carmack"}]
  const cs = pg.pgp.helpers.ColumnSet(
    ["name","lastname"],
    { table: "user.user_data" }
  );

  const query = pg.pgp.helpers.insert(rows, cs);

  pg.db
    .none(query)
    .then(data => {
      console.log("success");
      res.status(200).send("OK");
    })
    .catch(err => {
      res.status(400).send("error");
    });

error: relation "user.user_data" does not exist.

however, if I use raw query, it does detect the table:

var sql =
    "insert into user.user_data(name, lastname)" +
    "values($1,$2)";
    var values = [{"alex", "matos"},{"john","carmack"}];

    pg.db
    .none(sql, values)
    .then(data => {})
    .catch(err => {
        console.log(err);
        res.status(400).send("Error");
    });

here's my configuration file, where I get pg and db variables from:

const options = {
  promiseLib: promise,
  capSQL: true
};

//Postgres Connection
var pgp = require("pg-promise")(options);
var connectionString = `pg://${config.db.user}:${config.db.pass}@${
  config.db.url
}/${config.db.dbname}`;

var db = pgp(connectionString);

db.connect()
  .then(function(obj) {
    obj.done();
    console.log("connected");
  })
  .catch(err => {
    throw err;
  });


monitor.attach(options);

module.exports = {
  pgp: pgp,
  db: db
};

How can I fix this issue? I wanna use the first code because it's faster.

Thank you

edit: LOGS

INSERT INTO "user.user_data"("name","lastname") VALUES("alex", "matos")

how do i remove the quotes froms user.user_data?

vitaly-t · Accepted Answer · 2018-08-14 23:27:05Z

2

Try it like this:

const cs = pg.pgp.helpers.ColumnSet(
    ['name', 'lastname'], { table: { table: 'user_data', schema: 'user' } }
);

Or you can try like this:

const table = new pgp.helpers.TableName('user_data', 'user');
const cs = pg.pgp.helpers.ColumnSet(
    ['name', 'lastname'], { table }
);

More information here.

edited Aug 14, 2018 at 23:27

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

answered Aug 14, 2018 at 21:23

Sookie Singh

1,62313 silver badges17 bronze badges

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

4 Comments

vitaly-t Over a year ago

I am the author of pg-promise, and I have nothing to add here, good answer. Both options are good.

Blinhawk Over a year ago

Thank you! This worked. Quick question, is this supposed to be faster? Because it took around 30 minutes just to insert 70k rows.

Sookie Singh Over a year ago

@vitaly-t Thanks and wow! I didn't expect to see your comment on my answer! Blinhawk - I guess the author himself can give you more insights regarding the benchmarks.

vitaly-t Over a year ago

@SookieSingh To insert 70k of records one needs about 1 second. You must be doing something very wrong here... unless you are inserting huge binary columns.

Collectives™ on Stack Overflow

pg-promise. Query works when using raw query, but not when using helpers

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related