The column has a timestamp type without a time zone, but the expression is of type

Question

I am trying to insert records into my attempt to implement SCD2 in Redshift but I get an error.

DDL Target Table

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 for short, I'm trying to insert 20150901 16:34:02 in from_timestamp and 39991231 11:11:11.000 in 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 suggest how to solve this problem?

Answer 1

Postgres does not recognize 20150901 16:34:02 (your input) as a valid time and date format, so it takes a string.

Use a standard date format instead, preferably ISO-8601. 2015-09-01T16:34:02

SQLFiddle example

Answer 2

Just in case, someone here is trying to insert in postgresql a timestamp or timestampz from a variable in groovy or Java from a prepared statement and get the same error (like me), I managed to do this by setting the stringtype property to "unspecified" . According to the documentation :

Specify the type that will be used when binding PreparedStatement parameters through setString (). If stringtype is set to VARCHAR (default), the parameters will be sent to the server as varchar parameters. If stringtype is set to unspecified, the parameters will be sent to as untyped values, and the server will try to conclude that the corresponding 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 cannot modify 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) 

Using this set of properties, you can insert a timestamp as a string variable without the error that occurred in the title of the question. For instance:

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

Thus, the type of timestamp (from a string) is deduced correctly by postgresql. Hope this helps.