Monday, May 20, 2024
 Popular · Latest · Hot · Upcoming
140
rated 0 times [  144] [ 4]  / answers: 1 / hits: 38763  / 9 Years ago, sun, august 16, 2015, 12:00:00

I get error message like below , when I create this table just like others set column timestamp timestamp without time zone NOT NULL , but when I tried to insert 2015-08-16 18:51:05 to this table, then I get error, but other table is work, why and how to solve it?



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



[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.',


insert



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);
}
});
});
};

More From » sql

 Answers
7

This because you are inserting integer data to time stamp column.



Correct the following syntax:



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



In above query you are selecting 2 for TokenTimestamp that's why you are getting this error.



you should replace 2 with some date time format yyyy-mm-dd hh:mm:ss .



For example: '2015-08-07 05:00:01'


[#65400] Thursday, August 13, 2015, 9 Years  [reply] [flag answer]
Only authorized users can answer the question. Please sign in first, or register a free account.
turnerf

Total Points: 620
Total Questions: 101
Total Answers: 109

Location: French Polynesia
Member since Tue, Jul 7, 2020
4 Years ago
turnerf questions
;