时间戳

首页 > 解决方案 > 仅当名称和位置不匹配时才插入

javascript - 仅当名称和位置不匹配时才插入

问题描述

如果它具有唯一的名称和位置，我试图只将一条记录添加到我的 psql 表中。如果表中不存在条目（名称或位置），它可以插入记录，但如果名称已经存在，我的服务器会抛出错误以响应查询。到目前为止，这是我的代码：

app.post("/addCampground", async (req, res) => {
    const name = req.body.name;

    const location = req.body.location;
    const leng = req.body.maxlength;
    const elev = req.body.elevation;
    const site = req.body.sites;
    const pad = req.body.pad;

try{
    
const template = "INSERT INTO campgrounds (name, location, maxlength, elevation,
 sites, padtype) VALUES ($1, $2, $3, $4, $5, $6) SELECT name, location WHERE NOT 
EXISTS(SELECT name, location from campgrounds where name =$1 AND location =$2) ";
console.log(template);
const response = await pool.query(template, [name, location, leng, elev, site,
 pad], [name, location]);

res.json({status: "added", results:{name:name, location:location} });
}catch (err){
    res.json({status: "campground already in database"});
}

})

有问题的查询是：

const template = "INSERT INTO campgrounds (name, location, maxlength, elevation, sites, padtype) VALUES
 ($1, $2, $3, $4, $5, $6) SELECT name, location WHERE NOT EXISTS(SELECT name, location from campgrounds
 where name =$1 AND location =$2) ";

const response = await pool.query(template, [name, location, leng, elev, site, pad], [name, location]);

尝试添加名称匹配但位置不同的记录时出现错误：

TypeError: cb is not a function
    at Query.callback (/home/sbeg/db-class-350/practice/task4/node_modules/pg-pool/index.js:376:18)
    at Query.handleError (/home/sbeg/db-class-350/practice/task4/node_modules/pg/lib/query.js:128:19)
    at Client._handleErrorMessage (/home/sbeg/db-class-350/practice/task4/node_modules/pg/lib/client.js:335:17)
    at Connection.emit (events.js:315:20)
    at /home/sbeg/db-class-350/practice/task4/node_modules/pg/lib/connection.js:115:12
    at Parser.parse (/home/sbeg/db-class-350/practice/task4/node_modules/pg-protocol/dist/parser.js:40:17)
    at Socket.<anonymous> (/home/sbeg/db-class-350/practice/task4/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:295:12)
    at readableAddChunk (_stream_readable.js:271:9)

标签： javascriptsqlpostgresqlpostmanpsql

解决方案

sql 的正确语法：

INSERT INTO campgrounds (name, location, maxlength, elevation, sites, padtype) 
SELECT ($1, $2, $3, $4, $5, $6)
WHERE NOT EXISTS(SELECT 1 from campgrounds where name =$1 AND location =$2)

或者，如果您对名称、位置列有唯一索引，则可以使用on confflict：

INSERT INTO campgrounds (name, location, maxlength, elevation, sites, padtype) 
VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT unique_index DO NOTHING

推荐阅读