CODEWITHSUNDEEP
postgresqlexpress
jhamm
jhamm

Reputation: 25062

Postgres reports that a relation does not exist, but the table exists

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?

Upvotes: 4

Views: 12790

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78553

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:

Upvotes: 1

Related Questions