Storing timestamp in PostgreSQL gives an error: "cannot cast type bigint to timestamp without time zone"

Question

I've created my table with the following code:

CREATE TABLE "STORES" (
        "STOREGLOBALID"         SERIAL NOT NULL ,
        "STOREUPDATEDATETIME"   TIMESTAMP NOT NULL,
        "CHAINID"               BIGINT ,
        "CHAINNAME"             VARCHAR(50) ,
        "SUBCHAINID"            SMALLINT ,
        "SUBCHAINNAME"          VARCHAR(50) ,
        "STOREID"               SMALLINT ,
        "STORENAME"             VARCHAR(50) ,
        "STORETYPE"             SMALLINT ,
        "ADDRESS"               VARCHAR(50) ,
        "CITY"                  VARCHAR(50) ,
        PRIMARY KEY ("STOREGLOBALID")
);

When I try to insert timestamp by using the following SQL for preparing statement:

 INSERT INTO  "STORES" 
            ("STOREGLOBALID",
            "STOREUPDATEDATETIME",
            "CHAINID",
            "CHAINNAME",
            "SUBCHAINID",
            "SUBCHAINNAME",
            "STOREID",
            "STORENAME",
            "STORETYPE",
            "ADDRESS",
            "CITY") 
            VALUES (DEFAULT, cast(? as timestamp), ?, ?, ?, ?, ?, ?, ?, ?, ?)

And the following Java code:

ps = con.prepareStatement(sql);
ps.setLong(1, store.getChainId());
ps.setTimestamp(2, new Timestamp(1000000000)); //this value is only for test purposes, actual value gives the same error

I get the following error:

cannot cast type bigint to timestamp without time zone Position: 235

I understand that I need to provide a timestamp, but when I do it as following:

ps.setTimestamp(2, new Timestamp(1000000000), Calendar.getInstance(TimeZone.getTimeZone("UTC")));

I get the same error. What am I doing wrong? Thank you.

Answer 1

The first argument of your prepared statement is used in

cast(? as timestamp)

and the result is stored in STOREUPDATEDATETIME, of type TIMESTAMP. And you're passing a long (store.getChainId()) as argument. So you're trying to cast a long to a timestamp.

The second argument of your prepared statement is stored in CHAINID, of type BIGINT. And you're passing a Timestamp as argument (new Timestamp(1000000000)). So PostgreSQL tries to transform this timestamp into a bigint.

The SQL should be

INSERT INTO  "STORES" 
        ("STOREGLOBALID",
        "STOREUPDATEDATETIME",
        "CHAINID",
        "CHAINNAME",
        "SUBCHAINID",
        "SUBCHAINNAME",
        "STOREID",
        "STORENAME",
        "STORETYPE",
        "ADDRESS",
        "CITY") 
        VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

And the Java should be

ps.setTimestamp(1, new Timestamp(1000000000));
ps.setLong(2, store.getChainId());