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

Postgres isn't recognizing 20150901 16:34:02 (your input) as a valid time/date format, so it assumes it's a string.

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

SQLFiddle example