PostgreSQL ERROR: relation does not exist on INSERT Statement

Question

I am trying to have my code INSERT a row into my table called thoughtentries. It is in the public schema. I am able to run ths command while connected to my database using psql:

INSERT INTO thoughtentries VALUES('12/17/2016 14:10', 'hi');

The first column is of character type with length 17. The second column is of type text.

When I have my code attempt to INSERT using the same command above I get the error in my log:

ERROR: relation "thoughtentries" does not exist at character 13 STATEMENT: INSERT INTO thoughtentries VALUES('12/17/2016 14:11', 'hi');

I am using pg and pg-format to format the command. Here is my code to do this:

client.connect(function (err) {
  if (err) throw err
  app.listen(3000, function () {
    console.log('listening on 3000')
  })
  var textToDB = format('INSERT INTO thoughtentries VALUES(%s, %s);', timestamp, "'hi'")
  client.query(textToDB, function (err, result) {
    if (err) {
      console.log(err)
    }
    console.log(result)
    client.end(function (err) {
      if (err) throw err
    })
  })
})

How do I go about fixing this?

It tells you clearly that table `thoughtentries` doesn't exist. You need to fix your database. — vitaly-t, Dec 17 '16 at 19:27
@vitaly-t The table does exist, I have verified it by being able to INSERT rows into it with the command above. — Antonio Cucciniello, Dec 17 '16 at 21:46

score 2 · Accepted Answer · edited May 23 '17 at 12:08

2

Have you verified that the table was, in fact, created in the public schema?

SELECT *
FROM   information_schema.tables
WHERE  table_name = 'thoughtentries';

Once you have verified that, I see two possible explanations remaining:

You are connecting to a different database by mistake. Verify, in the same session, with:
```
select current_database();
```
Your search_path setting does not include the public schema. If that's the case, you can schema-qualify the table to fix: public.thoughtentries
- How does the search_path influence identifier resolution and the "current schema"

Aside: Save timestamps as data type timestamp, not character(17).
Actually, don't use character(n) at all:

Any downsides of using data type "text" for storing strings?

edited May 23 '17 at 12:08

Community

1
1

answered Dec 17 '16 at 19:28

Erwin Brandstetter

539,169
125
977
1,137

How would I know if I am connecting to a different database through my code? I have tried public.thoughtentries and that gives the same error message. I am going to look into fixing my search path more. – Antonio Cucciniello Dec 17 '16 at 21:49
@AntonioCucciniello: Check that you are connecting to the same database at the same hostand port. And I added some more above. – Erwin Brandstetter Dec 17 '16 at 22:07
Thank you for that update. I tried that command, and it displayed a bunch of stuff but at the end it showed my database, the table(thoughtentries), the user and the schema(public). So It is working there. It is possible that my code is connecting to another database and therefore does not see the table? I am not sure how `pg` determines which database is selected – Antonio Cucciniello Dec 18 '16 at 22:54
@AntonioCucciniello: Connect to your database explicitly. I added a test above. – Erwin Brandstetter Dec 19 '16 at 13:05

PostgreSQL ERROR: relation does not exist on INSERT Statement

1 Answers1