Relation does not exist error with pg-promise and postgresql?

Question

I am using pg-promise on Node 7.2.1 with PostgreSQL 9.4. I am using the code below to connect to the PostgreSQL server:

// pg-promise connection elements.

// Database initialization options.
const pg_options = {
    // Initialization options.
};

// Create an instance of the pg-promise library.
const g_pg_promise = require('pg-promise')(pg_options);

// Connection string for a local connection to the 'summarize' database.
const g_pg_connection =
{
    host: 'localhost',
    port: 5432,
    database: 'db_stats',
    user: 'db_user',
    password: '{password here}'
}

// Connect to the database.
const g_pg_database = g_pg_promise(g_pg_connection);

// Expose the database object globally.  The database object should only
//  be instantiated once and then shared.
module.exports = {
    g_pg_promise: g_pg_promise,
    g_pg_database: g_pg_database
};

I know the connection parameters are valid because I use the exact same values in other non-Node.JS apps to connect to the same PostgreSQL server. I also know that db_stats is a valid database name. I have worked with that database for quite a while with other non Node.JS apps and via pgAdmin 3.x.

However, when I attempt to connect using pg-promise I get the following error:

error: relation "db_stats" does not exist

I did see the SO post below:

Unable to query PostgreSQL database in NodeJS using pg-promise - "relation does not exist"

But that post did not make sense to me because I believe Vitaly, the author of pg-promise, was telling the poster that he did not have a table called users, when it looks to me that the poster was trying to access the database called users and he definitely had a database with that name.

In any case, I definitely have a database named db_stats so I'm not sure why I am getting this error. How can I solve this?

Answer 1

error: relation "db_stats" does not exist

The error you get from PostgreSQL is not referring to a database named "db_stats", but to a relation (either a table or a view) with that name.

That is, db_stats most probably appears in the FROM clause of a SELECT query (although it may be also a INSERT, UPDATE, DELETE, ...).

There is neither a db_stats table nor a view in your database. Or, maybe, it exists, but it exists in a schema that is not part of your current search_path.

To find out, check two things:

SELECT
    *
FROM
    information_schema.tables
WHERE
    table_name = 'db_stats' ;

If there is a table there... you already know which schema(s) contains it/them. Then, make sure that this schema is part of your search_path by means of:

SHOW search_path ;

You might have to execute this SQL statements by adding the adequate code to your application, and use a debugger to check what's returned in that environment.