javascript - column 是没有时区的 timestamp 类型，但 expression 是 integer 类型

Question

我收到如下错误消息，当我像其他人一样设置列时间戳 timestamp without time zone NOT NULL 时创建此表，但是当我尝试插入 2015-08-16 18 :51:05 到这个表，然后我得到错误，但其他表是工作的，为什么以及如何解决它？

CREATE TABLE IF NOT EXISTS "UserForgetPasswordPending"(
"UserForgetPasswordPendingId" SERIAL NOT NULL,
"Email" varchar(50) NOT NULL,
"TokenTimestamp" timestamp without time zone NOT NULL,
"Token" varchar(100) NOT NULL,
PRIMARY KEY ("UserForgetPasswordPendingId")
);
ALTER TABLE "UserForgetPasswordPending"
  OWNER TO db_admin;

错误

[error: column "TokenTimestamp" is of type timestamp without time zone but expression is of type integer]
  name: 'error',
  length: 216,
  severity: 'ERROR',
  code: '42804',
  detail: undefined,
  hint: 'You will need to rewrite or cast the expression.',

插入

var utc = moment(new Date()).unix();
var tokenTimestamp = moment.unix(utc).format('YYYY-MM-DD HH:mm:ss');

var upsertUserForgetPasswordPending = function(userEmail, tokenTimestamp, token) {
  return new Promise(function (fulfill, reject){
    var queryInsert = 'INSERT INTO "UserForgetPasswordPending" ("Email","TokenTimestamp","Token") SELECT $1,2,$3';
    var queryUpsert = 'UPDATE "UserForgetPasswordPending" SET "TokenTimestamp" = $2, "Token" = $3 WHERE "Email" = $1';
    var query = 'WITH upsert AS ('+queryUpsert+' RETURNING *) '+queryInsert+' WHERE NOT EXISTS (SELECT * FROM upsert)';
console.log(tokenTimestamp);
    dbClient.query(query, [userEmail,tokenTimestamp,token], function(error, result) {
      if (error) {
        reject(error);
      } else {
        fulfill(result);
      }
    });
  });
};

Answer 1

这是因为您要将整数数据插入到 time stamp 列。

更正以下语法:

var queryInsert = 'INSERT INTO "UserForgetPasswordPending ("Email","TokenTimestamp","Token") SELECT $1,2,$3';

在上面的查询中，您为 TokenTimestamp 选择了 2，这就是您收到此错误的原因。

您应该将 2 替换为某种日期时间格式 yyyy-mm-dd hh:mm:ss 。

例如:'2015-08-07 05:00:01'