3

so I psql'd and created table users;

 CREATE TABLE users (
    id integer NOT NULL,
    username text
);

I am able to grab rows by doing SELECT * FROM users;

However, when I use node.js with the library module pg to make calls I get the infamous relation does not exist. 

const createQuery = {text: "INSERT INTO users(id, username) VALUES($1)",values: values}
const { rows } = await db.query(createQuery);

I wasn't running into this issue before a complete server migration.

user299709
  • 4,922
  • 10
  • 56
  • 88
  • there is obviusly not somewhere a default schema set, is there? – Renato Apr 23 '20 at 05:08
  • not sure what you mean by default schema? I tried creating the tables with `public.users` but the same error message. I tried everything like `SELECT pg_catalog.set_config('search_path', '', true);` and `SET search_path TO schema,public;` but still running into this same issue. – user299709 Apr 23 '20 at 05:09
  • What is the database name? can you share the query which executes by code?? – Saeed Apr 23 '20 at 09:25
  • Make `values: [ ['id1': 'username1'], ['id2', 'username2'], ... ];` Then do, `db.query("INSERT INTO users(id, username) VALUES ?", [values], callback)`. Check if it works... – vrintle Apr 26 '20 at 02:52

3 Answers3

1

There can two possible reasons for this situation:

  1. You have created this relation in a different schema. PostgreSQL allows you to organize your relations into different namespaces called schemas. To specify a schema CREATE TABLE myschema.users (...), similarly, CREATE TABLE myschema.users. If the schema name is not specified, PostgreSql will use the defualt schema, usually it's public. To check your which schema does your relation belongs to run this query, SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 'users'.
  2. You can check which database and host you are connected to by using the SELECT current_database() and SELECT inet_server_addr() queries, just in case if you have connected to wrong database/host.
Hassan Rehan
  • 21
  • 6
0

The most likely option is that the table was created in a different schema than the default schema that you are using from node.js.

You can search the schema your table belongs to by querying table information_schema.tables:

select table_schema from information_schema.tables where table_name = 'users';

With this information at hand, you can now qualify your table name with the proper schema name in the query that you are running from node.js. Assuming that the schema name is myschema, you would go:

INSERT INTO myschema.users(id, username) VALUES($1)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I'm using PGAdmin 4 and put the schema's name in front of the relation's name as you suggested and didn't work. It worked when i put the schema's name and "." the I pressed the [Ctrl + Space keys], then PGAdmin4 listed all the relations and I chose the one i wanted. For some reason it worked despite the name of the relation was correct when I typed. – Jhonnatas Alencar Dec 30 '22 at 16:00
0

By default when you don't specify any schema, postgres points out to public schema. Please use schema name/ database name as along with the table name. Make sure you have provided proper configurations of the database within your code. If the configurations are proper and even you no need to provided schema alias within the query.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53