pg-promise set schema NodeJs

Question

I am using pg-promise to work with nodeJS and postgres, but when I try to query my User table, it retrieves the superUser. I think it is related to the fact that I don't have a schema set yet, so the code searches on the default 1. How can I set the schema on the code?

I tried this:

var express = require('express');
var app = express();
const PORT = 8080;

var pgp = require('pg-promise')(/*options*/)
var db = pgp('postgres://postgres:randompassword@localhost:5432/appname')

app.listen(PORT,function() {
    console.log("listening to port: " + PORT);
})

db.any('SELECT * FROM User')
    .then(function(data) {
        console.log(data);
    })
    .catch(function(error) {
        console.log(error);
    });

Default schema is `public`, and you don't need to set it. Therefore, not sure what issue you are having. But if your table is capitalized, use `"User"` instead. Try to revise your question. Also, there is no point passing in `[true]` into the query that expects no parameters. — vitaly-t, May 11 '17 at 16:46
@vitaly-t I am facing the same issue: I am connecting to a database correctly, I can query any table other than user and I get the expected answers. But when I query the user table, I get the user which I use to log in to the database. Any advice? (the capitalisation suggestion does not work) — ACEG, Jun 09 '17 at 15:28
@Antonio Costa Helpful colleague gave me a hint, posting here in case you still need it: surround user with quotes (SELECT * FROM "user"), otherwise SQL makes wrong assumptions. — ACEG, Jun 09 '17 at 15:37

score 0 · Answer 1 · answered Jun 09 '17 at 21:51

Your problem is not the schema, it is the table name.

User (case-insensitive) is a reserved reference to the currently logged in database user.

The best is not to use a table with such name, or use plurified - Users, then you won't have any problem.

But if you must use the table with name User then you will always have to put it in double quotes - "Users" (case-sensitive), so the server can tell the difference.

pg-promise set schema NodeJs

1 Answers1

Linked