Invalid Input syntax for Integer postgresql error

Question

I am trying to run the delete query from app.js file to postgresql database but every time I am getting error: invalid input syntax for integer: "undefined" Below is my app.js file code:

var express = require('express'),
    path = require('path'),
    bodyParser = require('body-parser'),
    cons = require('consolidate'),
    dust = require('dustjs-helpers'),
    pg = require('pg-promise'),
    pgdb = require('pg'),
    app = express();


//DB Connection
const config = {
    user: 'Nasreen',
    database: 'Inventory1',
    password: 'test',
    port: 5432                  //Default port, change it if needed
};

const pool = new pgdb.Pool(config);


//assign dust engine 
app.engine('dust', cons.dust);

//set default ext
app.set('view engine', 'dust');
app.set('views', __dirname + '/views');

//set public folder
app.use(express.static(path.join(__dirname, 'public')));

//Body parser middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));

app.get('/', function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            return console.error('error', err)
        }

        client.query('SELECT * FROM "Products"', function (err, result) {
            if (err) {
                return console.error('error running query', err);
            }
            res.render('index', { Products: result.rows });
            done();
        });
    });
});

app.post('/add', function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            return console.error('error', err)
        }

        client.query('INSERT INTO "Products" ("Name", "Purchase_Qty", "Purchase_Rate") VALUES($1, $2, $3)',
            [req.body.Name, req.body.Purchase_Qty, req.body.Purchase_Rate]);

        done();
        res.redirect('/');
    });
});

app.delete('/delete/:ID', function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            return console.error('error', err)
        }
        client.query('DELETE FROM "Products" WHERE "ID" = $1',
            [req.params.ID]);

        done();
        res.sendStatus(200);
    });
});


app.post('/edit', function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            return console.error('error', err)
        }

        client.query('UPDATE "Products" SET "Name"= $1, "Purchase_Qty"= $2, "Purchase_Rate"= $3 WHERE "ID"= $4',
            [req.body.Name, req.body.Purchase_Qty, req.body.Purchase_Rate, req.body.ID]);

        done();
        res.redirect('/');
    });
})

INSERT is working fine with double quotes for column names and table name but delete and edit wont't work. Can someone plz help!!

Naor Levi Naor Levi · Accepted Answer · 2020-01-18T14:02:26

You don't need to quote the tables' names and the columns' names.
Change the delete statement to:

"DELETE FROM Products WHERE ID = '$1'";

Change the update statement to:

"UPDATE Products SET Name= '$1', Purchase_Qty= '$2', Purchase_Rate= '$3' WHERE ID= '$4'";

EDIT: Try this:

client.query(`DELETE FROM "Products" WHERE "ID" = ${req.params.ID}`);

It's a function overloading for query. Using template string should solve the issue.

Instead of:

client.query('DELETE FROM "Products" WHERE "ID" = $1',[req.params.ID]);

Invalid Input syntax for Integer postgresql error

1 Answers