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

9
votes

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?

2
sqlpostgresqlsubquery
I'd say there is a blanks only value (' ') somewhere and that cannot be cast to a timestamp. Another good reason not to store dates or timestamp in varchar columns! – a_horse_with_no_name
You can find those rows using select * from the_table where valueTS ~ '[ ]+'. – a_horse_with_no_name
@a_horse_with_no_name if you want to add that as an answer, I will be happy to upvote. – Chris Travers

2 Answers

7
votes

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.

1
votes

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.