I have an express
app that I am connecting to my Postgres
db. Here is my code:
var express = require('express');
var app = express();
var pg = require('pg').native;
var connectionString = process.env.DATABASE_URL || 'postgres://localhost:5432/isx';
var port = process.env.PORT || 3000;
var client;
app.use(express.bodyParser());
client = new pg.Client(connectionString);
client.connect();
app.get('/users', function(req, res) {
'use strict';
console.log('/users');
var query = client.query('SELECT * FROM users');
query.on('row', function(row, result) {
result.addRow(row);
});
query.on('end', function(result) {
console.log(result);
res.json(result);
});
});
I go to my local Postgres
and look at the isx
db and here are the tables available.
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | projects | table | postgres
public | users | table | postgres
(2 rows)
But when I try to hit the users
table I get this error Error: relation "users" does not exist
.
The relation users
exists. I have checked and I am connected to the instance of Postgres
that I thought I was connected to. What else can I be missing?
Check for potential permission problems, e.g. an incorrect search path, or incorrect permissions, or something to that order. You could run this query to show all available tables, for instance:
select relname
from pg_class c
where pg_table_is_visible(c.oid)
and relkind = 'r'
and relname not like E'pg\_%';
It might also be worth looking into case-sensitivity related issues. For instance, perhaps the pg
library is adding double-quotes around lowercase identifiers, and you created tables with CamelCase, or something to that order.
Then check the search_path
if relevant:
show search_path;
If the path is correct, then check permissions, for instance using:
select usename, nspname || '.' || relname as relation,
case relkind when 'r' then 'TABLE' when 'v' then 'VIEW' end as relation_type,
priv
from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
pg_user,
(values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
where relkind in ('r', 'v')
and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
and not (nspname ~ '^pg_' or nspname = 'information_schema')
order by 2, 1, 3, privorder;
Taken from: Find out if user got permission to select/update/... a table/function/... in PostgreSQL
If relevant, fix the permissions using alter schema
and/or alter table
:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With