title: Postgres -> [22007] ERROR: invalid input syntax for type timestamp: " "
body: <p>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.</p>
  
  <pre><code>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 &lt;= now();
  </code></pre>
  
  <p>However when I run this I get the error message.</p>
  
  <blockquote>
    <p>[2013-01-09 14:04:30] [22007] ERROR: invalid input syntax for type
    timestamp: " "</p>
  </blockquote>
  
  <p>When I run the subquery on its own I get data like this...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Which gives output like this...</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>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?</p>