Столбец имеет временную метку типа без часовой пояс, но выражение имеет тип

Я пытаюсь вставить записи в свою попытку реализовать SCD2 в Redshift но получите сообщение об ошибке.

Целевая таблица DDL

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
 )

Это инструкция insert:

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

и для краткости, я пытаюсь вставить 20150901 16:34:02 в from_timestamp и 39991231 11:11:11.000 в to_timestamp.

и получим

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

Кто-нибудь может предложить, как решить эту проблему?

2 ответа

Postgres не распознает 20150901 16:34:02 (ваш ввод) как допустимый формат времени и даты, поэтому он принимает строку.

Используйте вместо этого стандартный формат даты, предпочтительно ISO-8601. 2015-09-01T16:34:02

Пример SQLFiddle


На всякий случай кто-то здесь пытается вставить в postgresql a timestamp или timestampz из переменной в groovy или Java из подготовленного оператора и получить ту же ошибку (как и я), я удалось сделать это, установив свойство stringtype в "unspecified". Согласно documentation:

Укажите тип, который будет использоваться при привязке параметров PreparedStatement через setString(). Если stringtype установлен в VARCHAR (по умолчанию), параметры будут отправляться на сервер как параметры varchar. Если stringtype установлен на неуказанный, параметры будут отправлены на как нетипизированные значения, и сервер попытается сделать вывод о том, что соответствующий тип. Это полезно, если у вас есть существующее приложение который использует setString() для установки параметров, которые на самом деле являются некоторыми другими типа, например целые числа, и вы не можете изменить приложение для использования соответствующего метода, такого как setInt().

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

С помощью этого набора свойств вы можете вставить временную метку в виде строковой переменной без ошибки, возникшей в заголовке вопроса. Например:

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

Таким образом, тип метки времени (из строки) выводится корректно postgresql. Надеюсь, это поможет.

licensed under cc by-sa 3.0 with attribution.