Column is of type timestamp without time zone but expression is of type character

Question

I'm trying to insert records on my trying to implement an SCD2 on Redshift but get an error.

The target table's DDL is

CREATE TABLE ditemp.ts_scd2_test (     id INT     ,md5 CHAR(32)     ,record_id BIGINT IDENTITY     ,from_timestamp TIMESTAMP     ,to_timestamp TIMESTAMP     ,file_id BIGINT     ,party_id BIGINT     ) 

This is the insert statement:

INSERT INTO ditemp.TS_SCD2_TEST(id, md5, from_timestamp, to_timestamp)  SELECT TS_SCD2_TEST_STAGING.id     ,TS_SCD2_TEST_STAGING.md5     ,from_timestamp     ,to_timestamp FROM (     SELECT '20150901 16:34:02' AS from_timestamp         ,CASE              WHEN last_record IS NULL                 THEN '20150901 16:34:02'             ELSE '39991231 11:11:11.000'             END AS to_timestamp         ,CASE              WHEN rownum != 1                 AND atom.id IS NOT NULL                 THEN 1             WHEN atom.id IS NULL                 THEN 1             ELSE 0             END AS transfer         ,stage.*     FROM (         SELECT id         FROM ditemp.TS_SCD2_TEST_STAGING         WHERE file_id = 2         GROUP BY id         HAVING count(*) > 1         ) AS scd2_count_ge_1     INNER JOIN (         SELECT row_number() OVER (                 PARTITION BY id ORDER BY record_id                 ) AS rownum             ,stage.*         FROM ditemp.TS_SCD2_TEST_STAGING AS stage         WHERE file_id IN (2)         ) AS stage         ON (scd2_count_ge_1.id = stage.id)     LEFT JOIN (         SELECT max(rownum) AS last_record             ,id         FROM (             SELECT row_number() OVER (                     PARTITION BY id ORDER BY record_id                     ) AS rownum                 ,stage.*             FROM ditemp.TS_SCD2_TEST_STAGING AS stage             )         GROUP BY id         ) AS last_record         ON (                 stage.id = last_record.id                 AND stage.rownum = last_record.last_record                 )     LEFT JOIN ditemp.TS_SCD2_TEST AS atom         ON (                 stage.id = atom.id                 AND stage.md5 = atom.md5                 AND atom.to_timestamp > '20150901 16:34:02'                 )     ) AS TS_SCD2_TEST_STAGING WHERE transfer = 1 

and to short things up, I am trying to insert 20150901 16:34:02 to from_timestamp and 39991231 11:11:11.000 to to_timestamp.

and get

ERROR: 42804: column "from_timestamp" is of type timestamp without time zone but expression is of type character varying

Can anyone please suggest how to solve this issue?

Answer 1

Just in case someone ends up here trying to insert into a postgresql a timestamp or a timestampz from a variable in groovy or Java from a prepared statement and getting the same error (as I did), I managed to do it by setting the property stringtype to "unspecified". According to the documentation:

Specify the type to use when binding PreparedStatement parameters set via setString(). If stringtype is set to VARCHAR (the default), such parameters will be sent to the server as varchar parameters. If stringtype is set to unspecified, parameters will be sent to the server as untyped values, and the server will attempt to infer an appropriate type. This is useful if you have an existing application that uses setString() to set parameters that are actually some other type, such as integers, and you are unable to change the application to use an appropriate method such as setInt().

Properties props = [user : "user", password: "password",  driver:"org.postgresql.Driver", stringtype:"unspecified"] def sql = Sql.newInstance("url", props) 

With this property set, you can insert a timestamp as a string variable without the error raised in the question title. For instance:

String myTimestamp= Instant.now().toString() sql.execute("""INSERT INTO MyTable (MyTimestamp) VALUES (?)""", [myTimestamp.toString()] 

This way, the type of the timestamp (from a String) is inferred correctly by postgresql. I hope this helps.