无法使用pg-promise在NodeJS中查询PostgreSQL数据库-“关系不存在”； [英] Unable to query PostgreSQL database in NodeJS using pg-promise - "relation does not exist"

问题描述

我试图让pg-promise在NodeJS中工作，以允许我连接到PostgreSQL数据库。节点和postgre都在运行Ubuntu的codeanywhere节点框中运行。

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.

以下是相关代码：

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);
    });

运行 node bot.js 打印以下内容：

 { [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' } 

然后将以下内容打印到 /var/log/postgresql/postgresql-9.3-main.log ：

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

$不存在关系用户 b
$ b

我做错了什么？我猜应该是db.any（）的东西，因为下面的代码确实起作用：

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);
    });

输出是看起来像 db的东西的返回对象，但这不是我所需要的...

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

我还看到了其他stackoverflow问题，其中提到大写是一个问题。具体来说，他们说postgresql将使您的输入全部变为小写而没有双引号。要消除任何此类相关性概念：

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) 

该关系确实存在。

我缺少什么？

推荐答案

错误告诉您没有<$数据库中的c $ c> users 表。您需要先创建表，然后才能从中进行选择。请记住，表名区分大小写，因此，如果您创建了 Users 表，则查询将无法工作。

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

您应尝试使用连接到数据库psql -d users （在这种情况下， users 是数据库的名称，不要与同名的表混淆）命令行。从那里，您可以编写查询以独立于应用程序代码测试它们。如果您在连接到 users 数据库时遇到问题，则始终可以使用psql连接到默认数据库，然后键入 \l 或 \list 列出所有postgres知道的数据库。

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.

请注意，Postgres本身有一个内置名为 postgres 的数据库，该数据库具有自己的 users 表来管理对其他数据库的访问控制。您可以通过查看命令提示符来告诉您在psql中连接了哪个数据库。如果显示 postgres = ，则说明您位于postgres数据库中，而不是您自己的数据库中。

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.

这篇关于无法使用pg-promise在NodeJS中查询PostgreSQL数据库-“关系不存在”；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！