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?

Solution 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.

Solution 2

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.

Comments

[sgeddes]

Have you tried running your query without the WHERE criteria (but keeping the subquery)? Does it return any blank (' ') records for valuets?