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

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

• javascript
• sql
• node.js
• postgresql

• Please show the exact insert statement you are using. Unrelated, but: using quoted identifiers usually creates more problems than they are worth it. – user330315 Commented Aug 16, 2015 at 10:58
• @a_horse_with_no_name I update insert function I use, what do you mean quoted identifiers ?? do you mean in query I use somethin like CREATE TABLE IF NOT EXISTS "UserForgetPasswordPending" .. ? that because I use uppercase naming ... – user1775888 Commented Aug 16, 2015 at 11:05
• What programming language is that? And what data type is the variable tokenTimestamp? Seems like you are passing an integer value there, not a timestamp. I'm referring to the use of double quotes around identifiers, which makes them case sensitive. In the long run that usually creates more problems that they are worth (some dispute the "worthiness" of quoted identifiers in the first place). – user330315 Commented Aug 16, 2015 at 11:07
• 1 I have no idea how JavaScript passes variables, put in plain SQL this works just fine: sqlfiddle.com/#!15/bfd35/1 Maybe it's caused by your usage of select instead of values for the INSERT statement, but I can't tell if JavaScript messes with that somehow. – user330315 Commented Aug 16, 2015 at 11:54
• 1 You have a typo: SELECT $1,2,$3 should be SELECT $1,$2,$3. – IMSoP Commented Aug 16, 2015 at 12:08

3 Answers 3

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'

For me casting the type explicitely worked. Please note, I used joda Datetime

@CreatedDate
@Column(name = "created_on", updatable = false, nullable = false)
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
private DateTime createdOn;

Add timestamp like this '2025-08-07 05:00:01'