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?

Solution

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:

• http://www.postgresql.org/docs/current/static/sql-alterschema.html
• http://www.postgresql.org/docs/current/static/sql-altertable.html

Comments

[jhamm]

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 ] }.

[Denis de Bernardy]

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.

[jhamm]

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.

[Denis de Bernardy]

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.

[jhamm]

It is $user, public. How do I check for the other problems?

[Denis de Bernardy]

\d user in psql, else stackoverflow.com/questions/946804/…

[jhamm]

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.

[Denis de Bernardy]

Edited accordingly. The same for your question's title, since it wasn't very discoverable using search engines.

[marcopolo]

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...