12

I am getting a [error: relation "causes" does not exist] error from my node app. The relation does exist, I'm not sure what the problem is.

I created the table with

CREATE TABLE causes (

cause_id bigint NOT NULL default nextval('causes_cause_id_seq'::regclass),
cause_name varchar(40) NOT NULL,
goal integer,
sponsor varchar(30),
organization varchar(30),
submitter varchar(30),
address varchar(34),
balance numeric

);

This is the query that's giving the error:

client = pg.connect(connectionString, function(err, client, done){
    if(err) console.log(err);

    client.query('INSERT INTO causes (cause_name, goal, organization, sponsor, submitter) VALUES ($1,$2,$3,$4,$5) RETURNING *', r, function(err, result){
    if(err) console.log(err);
    });
});
Kinnard Hockenhull
  • 2,790
  • 5
  • 27
  • 34

7 Answers7

4

I'm not sure if any of you facing the similar situation like I did.

Please make sure you are using correct user/password to the correct database host.

I found I connected to a wrong database :(

Weijing Jay Lin
  • 2,991
  • 6
  • 33
  • 53
  • 1
    sounds stupid, but also correct in my case. LOL. I used an environment variable for DB URL and forgot to source the file before starting the server. – Nhat Dinh Aug 16 '20 at 13:49
3

Directly before your client.query('INSERT...') call, run the following to ensure that your relation is accessible on the current connection: 

client.query('SELECT * FROM pg_catalog.pg_tables', function(err, result) {
  console.log(result);
});

If you don't see your causes relation among the results, then either the relation doesn't exist, or it was created under a different user.

chejazi
  • 56
  • 3
1

This is probably a case folding issue. See this answer and the PostgreSQL documentation on SQL syntax.

After edit: Looks like it isn't a case folding issue. Check search_path (SHOW search_path or SELECT current_setting('search_path')) and compare it to the schema the table is in (\dt+ tablename) to make sure the table is on the client's path.

Also make sure you're connecting to the same database.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • They both say public. The connection string I'm using for node-postgres is `'pg://myusername:mypassword@localhost/tzedakahbits';` Do I need to specify something so that it can connect to the database? – Kinnard Hockenhull Nov 13 '13 at 21:35
  • @KinnardHockenhull Well, when you use `psql`, are you connecting to `localhost` and the database name `tzedakahbits` there too? Try explicitly specifying the port after localhost, making sure it's the same port used by `psql` (run `SHOW port` in `psql` to see the port of of the DB you're connected to). Sometimes people are running more than one copy of PostgreSQL and get confused. – Craig Ringer Nov 13 '13 at 22:48
  • I specified the port, it still says the relation does not exist – Kinnard Hockenhull Nov 13 '13 at 22:57
  • 1
    @KinnardHockenhull What about if you try to query it from within `psql`? `SELECT * FROM therelationname`? – Craig Ringer Nov 13 '13 at 22:57
  • 1
    Ok, I think I figured it out, I have two databases, one where the table exists and the other where it doesn't – Kinnard Hockenhull Nov 13 '13 at 23:01
1

I had the same problem. I was querying newly created table Example. This was my code:

const { Pool, Client } = require('pg');
const dbClient = new Client({
  user: 'postgres',
  host: 'localhost',
  database: 'postgres',
  password: 'test',
  port: 5432,
});
dbClient.connect();
dbClient.query('SELECT * from Example', (err, res) => {
    console.log(err, res);
    dbClient.end();
});

I double checked connection parameters and any possible typos. It turned out that in pg you need to wrap the table name in quotes:

dbClient.query('SELECT * from "Example"', (err, res) => {
    console.log(err, res);
    dbClient.end();
});
kukis
  • 4,489
  • 6
  • 27
  • 50
  • 3
    This is only if it contains capital letters i believe – Catfish May 22 '19 at 18:29
  • When the table contains camelcase naming format, it needs to be wrapped with double quote. (but not the schema name). i.e ) client.query('select * from scmcore."CustomersStatistics" limit 10') – jetulis Oct 28 '19 at 14:14
1

If you're connecting using a Postgres URL, specify the database explicitly in it. Sometimes if you don't, whatever lib you're using defaults to your username as the database, which may be the wrong one. I just had this happen to me. See PostgreSQL Connection URL

sudo
  • 5,604
  • 5
  • 40
  • 78
0

I resolved my issue but setting my schema name before table name :

SELECT * FROM public.mytable

instead of

SELECT * FROM mytable
Keytrap
  • 421
  • 5
  • 14
-1

I had a similar error when trying to create a table with user input for table name and then insert into database.

The solution I found was to create the create table and insert into as two separate functions, call the functions back to back, but the second one with a delay.

Guiding code is below:

function createTable(){
// your create table query here
}

function pushData(){
// your insert into table query here
}

createTable()
setTimeout(postTable, 3000); // for 3 seconds, can make is less, I used 3 secs to be safe
CHNL
  • 1
  • 1