postgresql 列是没有时区的时间戳类型，但表达式是字符类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32336651/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Column is of type timestamp without time zone but expression is of type character
提问by user2518751
I'm trying to insert records on my trying to implement an SCD2 on Redshift but get an error.
我试图在尝试在 Redshift 上实现 SCD2 时插入记录，但出现错误。
The target table's DDL is
目标表的 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
)
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:02to from_timestampand 39991231 11:11:11.000to to_timestamp.
为了缩短事情，我试图插入20150901 16:34:02到from_timestamp和39991231 11:11:11.000到to_timestamp。
and get
并得到
ERROR: 42804: column "from_timestamp" is of type timestamp without time zone but expression is of type character varying
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?
任何人都可以请建议如何解决这个问题？
采纳答案by alroc
Postgres isn't recognizing 20150901 16:34:02(your input) as a valid time/date format, so it assumes it's a string.
Postgres 无法将20150901 16:34:02（您的输入）识别为有效的时间/日期格式，因此它假定它是一个字符串。
Use a standard date format instead, preferably ISO-8601. 2015-09-01T16:34:02
改用标准日期格式，最好是 ISO-8601。 2015-09-01T16:34:02
回答by Jaime Caffarel
Just in case someone ends up here trying to insert into a postgresql a timestampor a timestampzfrom 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 stringtypeto "unspecified". According to the documentation:
以防万一有人最终在这里尝试从准备好的语句中插入来自 groovy 或 Java 中的变量的 postgresql atimestamp或 atimestampz并得到相同的错误（就像我所做的那样），我设法通过将属性设置stringtype为"unspecified". 根据文档：
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().
指定绑定通过 setString() 设置的 PreparedStatement 参数时要使用的类型。如果 stringtype 设置为 VARCHAR（默认值），则此类参数将作为 varchar 参数发送到服务器。如果 stringtype 设置为未指定，参数将作为无类型值发送到服务器，服务器将尝试推断适当的类型。如果您的现有应用程序使用 setString() 来设置实际上是其他类型的参数（例如整数），并且您无法更改应用程序以使用适当的方法（例如 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.
这样，postgresql 可以正确推断时间戳的类型（来自字符串）。我希望这有帮助。
回答by Gene
Inside apache-tomcat-9.0.7/conf/server.xml
在 apache-tomcat-9.0.7/conf/server.xml 里面
Add "?stringtype=unspecified" to the end of url address.
For example:
?stringtype=unspecified在 url 地址末尾添加“ ”。例如：
<GlobalNamingResources>
<Resource name="jdbc/??" auth="Container" type="javax.sql.DataSource"
...
url="jdbc:postgresql://127.0.0.1:5432/Local_DB?stringtype=unspecified"/>
</GlobalNamingResources>
