role does not exist and syntax error for inserting query in NodeJs into postgresql

Question

I've created a table with the name of user in postgres which contains id, name, family columns (id is auto-increment). The query which I want to insert into the postgres likes the following:

var config = {
    user: 'username',
    database: 'databaseName',
    password: 'password',
    host: 'localhost',
    port: 5432,
    max: 10,
    idleTimeoutMillis: 30000
};

const pool = new postgre.Pool(config);
let query = "INSERT INTO user(name, family) VALUES ('name', 'family')";
pool.query(query, [], function (err, result) {
    if (err) {
        return console.error("error running query", err);
    }
    console.log("done!");        
});

But, I've got the following error:

Syntax error near user

Also, I have tried different approach to send the request liked the following:

const connectionString = "http://localhost:5432/databaseName?username=username&password=password";
const client = new postgre.Client(connectionString);
client.connect();
const query = client.query("INSERT INTO user(id, name, family) VALUES ('name', 'family')");
query.then(x => { console.log("done!")});
query.catch(x => { console.log(x)});

But I've got the following error:

role \"UserName\" does not exist

Also, the UserName (name of the server) in the body of the error is not the same as the username of the database.

I've checked the all possible fallacies such as the correctness of the username and password.

Therefore, the question is what is the problem?

OmG · Accepted Answer · 2017-06-17 07:35:18Z

1

All of these problems come from the table name. As mentioned in this post, the name of user is a reserved name in postgres. Therefore, these errors will be passed if change the name of the user table such as users.

answered Jun 17, 2017 at 7:35

OmG

19k13 gold badges69 silver badges96 bronze badges

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

2 Comments

vitaly-t Over a year ago

Renaming the table isn't the best solution. See the answer I posted ;)

OmG Over a year ago

@vitaly-t I saw this post stackoverflow.com/questions/11919391/… before. Indeed, I found the solutions, but mentioned here as a problem can be seen from the other window. In addition, for my case changing the name of the table is better. Anyhow, thanks for your response.

vitaly-t · Accepted Answer · 2017-06-17 07:39:32Z

1

user is a reserved word in PostgreSQL that refers to the currently logged in user. For that reason, in order to be able to use a table with name user, the name must always be inside double quotes - "user".

answered Jun 17, 2017 at 7:39

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Comments

secnigma · Accepted Answer · 2024-03-08 18:45:12Z

role "UserName" does not exist

Node shows the exact same error, even in the event of failure to establish a connection to the postgresql instance.

Since the Username and Password pair was already verified, ensuring the validity of DATABASE_HOST and DATABASE_PORT variables would be a good place to start, as Postgresql uses ports 5432 and 5433 interchangably.

Further more, Unix sockets can be set as node's DATABASE_HOST simply via localhost or it can be explicitly mentioned to the directory where the socket is located (usually at /var/lib/postgresql or /tmp for BSD systems) and Node will automatically find the socket name (looks like this: .s.PGSQL.5433), based on the configured DATABASE_PORT variable.

Same issue mentioned at the core pg module Github repo

Collectives™ on Stack Overflow

role does not exist and syntax error for inserting query in NodeJs into postgresql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related