Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
                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
                Jan 9, 2013 at 21:17
                Have you tried running your query without the WHERE criteria (but keeping the subquery)?  Does it return any blank (' ') records for valuets?
– sgeddes
                Jan 9, 2013 at 21:31
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.