javascript - 错误: invalid input syntax for type date: "" using NodeJS and PostgreSQL

Question

我正在使用 NodeJS 将数据插入数据库。如果用户未输入日期值，则 "" 将被发送到数据库。如果我这样做，就会出现此错误错误:日期类型的输入语法无效:“”如果出现这种情况我该怎么办？

const addAlbum = (request, response) => {
 const { title, date, description, id } = request.body;

 for (let i = 0; i < request.body.length; i++) {
  db.pool.query('INSERT INTO albums (title, date, description, id) VALUES ($1, $2, $3, $4) ON 
  CONFLICT (id) DO NOTHING RETURNING *' , [request.body[i].title, request.body[i].date, 
  request.body[i].description, request.body[i].id], (error, results) => {
    if (error) {
      throw error
    } else {
      console.log('INSERT ' + JSON.stringify(request.body));
    }
  })
 }
}

Answer 1

我认为，request.body[i].date是一个字符串，同样如果表中对应的列类型是date类型，那么它应该是YYYY-MM-DD 格式。您应该能够存储日期字符串值，将其转换为日期对象。尝试将 request.body[i].date 更改为:

new Date(request.body[i].date);

您可以进行以下练习:

const createTableText = `
CREATE TEMP TABLE dates(
  date_col DATE,
  timestamp_col TIMESTAMP,
  timestamptz_col TIMESTAMPTZ,
);
`
// create our temp table
await client.query(createTableText)
// insert the current time into it
const now = new Date()
const insertText = 'INSERT INTO dates(date_col, timestamp_col, timestamtz_col) VALUES ($1, $2, $3)'
await client.query(insertText, [now, now, now])
// read the row back out
const result = await client.query('SELECT * FROM dates')
console.log(result.rows)
// {
// date_col: 2017-05-29T05:00:00.000Z,
// timestamp_col: 2017-05-29T23:18:13.263Z,
// timestamptz_col: 2017-05-29T23:18:13.263Z
// }  

来源:https://node-postgres.com/features/types