column的类型为timestamp,没有时区,但expression的类型为integer
我得到如下的错误信息,当我创建此表时,就像其他人设置列时间戳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);
}
});
});
};
回答如下:
这是因为您要将整数数据插入time stamp
列。
更正以下语法:
var queryInsert = 'INSERT INTO "UserForgetPasswordPending ("Email","TokenTimestamp","Token") SELECT $1,2,$3';
在上面的查询中,您为2
选择TokenTimestamp
,这就是您收到此错误的原因。
你应该用一些日期时间格式2
替换yyyy-mm-dd hh:mm:ss
。
例如:'2015-08-07 05:00:01'