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?

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! — user330315
– user330315, Commented Jan 9, 2013 at 21:17
You can find those rows using select * from the_table where valueTS ~ '[ ]+'. — user330315
– user330315, Commented Jan 9, 2013 at 21:25

wickedone · Accepted Answer · 2013-02-01 15:20:22Z

8

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.

edited Feb 1, 2013 at 15:20

wickedone

5401 gold badge6 silver badges18 bronze badges

answered Jan 9, 2013 at 21:18

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sgeddes Over a year ago

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

Chris Travers · Accepted Answer · 2013-04-20 07:38:58Z

4

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.

answered Apr 20, 2013 at 7:38

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related