Join the Stack Overflow Community
Stack Overflow is a community of 6.8 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

I'm trying to get pg-promise working in NodeJS to allow me to connect to my PostgreSQL database. Both node and postgre are running in a codeanywhere node box running Ubuntu.

Here's the relevant code:

var pgp = require('pg-promise')();
var bot = new TelegramBot(token, { polling: true });
var db = pgp({
    host: 'localhost',
    port: 5432,
    database: 'users',
    user: 'postgres',
    password: '(pass here)'
});
db.any("select * from users")
    .then(function (data) {
        console.log(data);
    })
    .catch(function (error) {
        console.log(error);
    });

Running node bot.js prints the following:

 { [error: relation "users" does not exist]                                      
  name: 'error',                                                                
  length: 96,                                                                   
  severity: 'ERROR',                                                            
  code: '42P01',                                                                
  detail: undefined,                                                            
  hint: undefined,                                                              
  position: '15',                                                               
  internalPosition: undefined,                                                  
  internalQuery: undefined,                                                     
  where: undefined,                                                             
  schema: undefined,                                                            
  table: undefined,                                                             
  column: undefined,                                                            
  dataType: undefined,                                                          
  constraint: undefined,                                                        
  file: 'parse_relation.c',                                                     
  line: '984',                                                                  
  routine: 'parserOpenTable' }

And prints the following to /var/log/postgresql/postgresql-9.3-main.log:

2016-09-29 23:09:29 EDT ERROR:  relation "users" does not exist at character 15

What did I do wrong? I guess it should be something with db.any(), as the following code does work:

//db.any("select * from users")
db.connect()
    .then(function (data) {
        console.log(data);
    })
    .catch(function (error) {
        console.log(error);
    });

The output is a return of something that looks approximately like the db object, but that's not what I need...

I've seen other stackoverflow questions mentioning capitalization as an issue. Specifically, they say that postgresql will make your input all lowercase without double quotes. To dispell any such notion of relevance:

postgres=# \dt                                                                                             
         List of relations                                                                                 
 Schema | Name  | Type  |  Owner                                                                           
--------+-------+-------+----------                                                                        
 public | users | table | postgres                                                                         
(1 row)

The relation certainly does exist.

What am I missing?

share|improve this question
up vote 1 down vote

The error is telling you that there is no users table in your database. You need to create the table before you can select from it. Remember, the table name is case sensitive so if you created a Users table, your query won't work.

CREATE TABLE Users (...);
SELECT * FROM users; -- this will cause an error

You should try connecting to your database using psql -d users (users in this case is the name of your database, not to be confused with your table of the same name) on the command line. From there you can write queries to test them independently from your application code. In case you're having trouble connecting to your users database, you can always connect to the default database with psql and type \l or \list to list all databases postgres knows about.

Note that Postgres itself has a built in database named postgres that has its own users table to manage access control to other databases. You can tell which database you're connected to in psql by looking at the command prompt; If it says postgres= then you're in the postgres database, not your own.

share|improve this answer
    
See edit; table is called 'users' – Menasheh Sep 30 '16 at 4:53
    
What you're listing is the users table in the postgres database, not your database (which you've also named users). You can tell you're in the postgres db because of the postgres= prompt. – Soviut Sep 30 '16 at 4:55
    
Sigh... So it automatically got called postgres because that's what the username was? – Menasheh Sep 30 '16 at 4:57
    
No, you forgot the -d flag when you ran psql. -d <database_name> will connect you to your database that you created. so psql -d users is what your database settings indicate your database is called. – Soviut Sep 30 '16 at 4:59
    
Replacing 'users' with 'postgres' in the db.any call seems to work for me – Menasheh Sep 30 '16 at 5:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.