SQL Postgres -> [22007] 错误：类型时间戳的无效输入语法：“”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14246567/
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
Postgres -> [22007] ERROR: invalid input syntax for type timestamp: " "
提问by benstpierre
I have a table that stores a number of data types as a string. Sometimes this value is a string representing a timestamp. The following query is meant to join the RetsEntry and RetsProvider tables on a fieldname (specified in RetsProvider) then filter out the "systemid" column values where the timestamp of the entry is between two times.
我有一个表，将许多数据类型存储为字符串。有时这个值是一个表示时间戳的字符串。以下查询旨在将 RetsEntry 和 RetsProvider 表连接到一个字段名（在 RetsProvider 中指定），然后过滤掉条目时间戳介于两次之间的“systemid”列值。
SELECT
*
FROM (
SELECT
systemid,
cast(value AS TIMESTAMP) AS valueTS
FROM cabarets.retsentry, cabarets.retsprovider
WHERE cabarets.retsentry.providername = cabarets.retsprovider.name
AND cabarets.retsentry.systemname = cabarets.retsprovider.lastupdatefield) AS foo
WHERE foo.valueTS <= now();
However when I run this I get the error message.
但是，当我运行它时，我收到错误消息。
[2013-01-09 14:04:30] [22007] ERROR: invalid input syntax for type timestamp: " "
[2013-01-09 14:04:30] [22007] 错误：类型时间戳的输入语法无效：“”
When I run the subquery on its own I get data like this...
当我自己运行子查询时，我得到这样的数据......
SELECT
systemid,
cast(value AS TIMESTAMP) AS valueTS
FROM cabarets.retsentry, cabarets.retsprovider
WHERE cabarets.retsentry.providername = cabarets.retsprovider.name
AND cabarets.retsentry.systemname = cabarets.retsprovider.lastupdatefield
Which gives output like this...
这给出了这样的输出......
systemid valuets
'4705683' '2012-11-08 01:37:45'
'259534632' '2012-11-15 20:40:52'
'259536713' '2012-10-16 10:57:40'
'65815875' '2012-10-28 22:36:00'
'259540896' '2012-10-16 09:59:22'
'4707500' '2012-11-10 01:44:58'
Is the problem that postgres will not let you add a where clause based on a column that is an alias for a casted string column?
问题是 postgres 不允许您添加基于作为强制转换字符串列别名的列的 where 子句吗？
回答by sgeddes
Have you tried casting your WHEREclause:
你有没有试过铸造你的WHERE条款：
WHERE cast(foo.valueTS AS TIMESTAMP) <= now();
Also have you tried using CURRENT_TIMESTAMPinstead of now()?
你也试过使用CURRENT_TIMESTAMP代替now()吗？
Not sure what else it could be unless there are other data issues.
除非有其他数据问题，否则不确定它可能是什么。
回答by Chris Travers
Your problem is that you have invalid data. This is an important reason to use appropriate data types so you can catch errors on input rather than sorting out later. You can do something like the following to correct:
你的问题是你有无效的数据。这是使用适当的数据类型的一个重要原因，这样您就可以在输入时捕获错误而不是稍后进行整理。您可以执行以下操作来更正：
UPDATE the_table
SET value_ts = '1900-01-01 00:00:00'
WHERE value_ts ~ '[ ]+';
I am concerned though that if this is the problem you will find more.
我担心如果这是问题，你会发现更多。
