I'm playing with an app on heroku. It uses heroku postgresql but I have trouble inserting data to it. The code looks as follows:

const { Client } = require('pg');

function connect2DB(){
  const client = new Client({
    connectionString: process.env.DATABASE_URL,
    ssl: {
      rejectUnauthorized: false
    }
  });
  client.connect();
  return client;
}

function createLastUseTableIfNotThere(){
  try{
    const client = connect2DB();
    client.query('CREATE TABLE IF NOT EXISTS "lastUse" ("clientKey" VARCHAR(255), "lastDate" date, PRIMARY KEY ("clientKey"));', (err, res) => {
      if(err){
        console.error(`createLastUseTableIfNotThere db query failed!`);
        console.error(err);
      }else{
        console.log(res);
      }
      client.end();
    });
  } catch(e){
    console.error(`createLastUseTableIfNotThere failed!`);
    console.error(e);
  }
}

function lastUseMiddleware(req, res, next){
  const clientKeyValue = req.context.clientKey;
  try{
    if(clientKeyValue != undefined){
      console.log(`lastUseMiddleware: upserting clientKey ${clientKeyValue}`);
      const client = connect2DB();
      client.query(`
        INSERT INTO "lastUse"("clientKey","lastDate")
        VALUES("${clientKeyValue}",now())
        ON CONFLICT ("clientKey")
        DO UPDATE SET "lastDate" = now();`,
        (err, res) => {
          if(err){
            console.error(`lastUseMiddleware: upserting failed!`);
            console.error(err);
          }
          client.end();
      });
    }else{
      console.warn(`lastUseMiddleware: clientKey is undefined: ${clientKey}`);
    }
  }catch(e){
    console.error(`lastUseMiddleware thrown`);
    console.error(e);
  }
  next();
}

Calling the createLastUseTableIfNotThere function worked properly and I can see the table created on heroku. Calling the lastUseMiddleware function generates following errors in logs:

lastUseMiddleware: upserting clientKey 3f825846-791c-3c5b-8916-4e2c827165b2

lastUseMiddleware: upserting failed!

error: column "3f825846-791c-3c5b-8916-4e2c827165b2" does not exist.

My intention is to add new row to the table with given clientKey and current date. Looks like the clientKey is treated as column identifier. Any idea why it is like that? Maybe the clientKey field should not be used as primary key in the table?

CodePudding user response：

Postgres treats double quotes as columns. You could use single column.

Like,

VALUES('${clientKeyValue}',now())