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?

Answer 1

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:

1. You are connecting to a different database by mistake. Verify, in the same session, with:

   select current_database();
   

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