Postgres -> [22007] ERROR: invalid input syntax for type timestamp: " "

Question

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.

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: " "

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?

Answer 1

Have you tried casting your WHERE clause:

WHERE cast(foo.valueTS AS TIMESTAMP) <= now();

Also have you tried using CURRENT_TIMESTAMP instead of now()?

Not sure what else it could be unless there are other data issues.