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

Question

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?

I put a `console.log` after that statement and it it prints out the right side of the logical operator, `postgres://localhost:5432/isx`. The `process.env.DATABASE_URL` is just for when I deploy to `Heroku`. — jhamm, Dec 22 '13 at 13:02

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

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:

edited May 23 '17 at 11:45

Community

1
1

answered Dec 22 '13 at 15:52

Denis de Bernardy

75,850
13
131
154

I ran this search in my express app: `SELECT table_name FROM information_schema.tables WHERE table_schema="public" AND table_type="BASE TABLE"`. The response I got from the query is: `{ "command": "SELECT", "rowCount": 1, "oid": null, "rows": [ { "table_name": "users" } ], "fields": [ { "name": "table_name", "dataTypeID": 1043 } ], "_parsers": [ null ] }`. – jhamm Dec 22 '13 at 17:51
That query asks the DB what tables are in the public schema. It's different from the query I suggested, which asks the DB what tables you can actually see. If your search path (which you can see by running `SHOW search_path;`) or your permissions don't allow you to see tables, they won't show in the query I suggested. – Denis de Bernardy Dec 22 '13 at 18:16
I don't understand the public schema piece, but that is the issue. Here is the result I got from your query: `{ "command": "SELECT", "rowCount": 0, "oid": null, "rows": [], "fields": [ { "name": "relname", "dataTypeID": 19 } ], "_parsers": [ null ] }`, so the table isn't available. – jhamm Dec 22 '13 at 18:23
1

Good. Now double check a) your search path (using `show search_path;`). If it's set to `"$user", public` (which is hopeful), it means you've a permission problem on the table. E.g. not the owner, no select access, something. – Denis de Bernardy Dec 22 '13 at 18:25
It is `$user, public`. How do I check for the other problems? – jhamm Dec 22 '13 at 18:38
`\d user` in `psql`, else http://stackoverflow.com/questions/946804/find-out-if-user-got-permission-to-select-update-a-table-function-in-pos – Denis de Bernardy Dec 22 '13 at 18:41
Make a proper answer so I can accept it. This could definitely be something that someone could use in the future. Thanks for all the help. – jhamm Dec 22 '13 at 19:00
Edited accordingly. The same for your question's title, since it wasn't very discoverable using search engines. – Denis de Bernardy Dec 22 '13 at 19:05
Regarding the search path: set search_path to public is OK, but set search_path to 'public' is not. Stupid error, i.e. a real error... – marcopolo Feb 21 '19 at 11:40

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

1 Answers1