Postgres -> [22007] ERROR: Ungültige Eingabesyntax für Typ timestamp: " "

Question

Ich habe eine Tabelle, die eine Reihe von Datentypen als Zeichenfolge speichert. Manchmal ist dieser Wert eine Zeichenfolge, die einen Zeitstempel darstellt. Die folgende Abfrage soll die Tabellen RetsEntry und RetsProvider anhand eines Feldnamens (in RetsProvider angegeben) verbinden und dann die Werte der Spalte "systemid" herausfiltern, bei denen der Zeitstempel des Eintrags zwischen zwei Zeiten liegt.

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();

Wenn ich dies jedoch ausführe, erhalte ich die Fehlermeldung.

[2013-01-09 14:04:30] [22007] ERROR: Ungültige Eingabesyntax für Typ Zeitstempel: " "

Wenn ich die Unterabfrage allein ausführe, erhalte ich Daten wie diese...

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

Das ergibt eine Ausgabe wie diese...

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'

Ist das Problem, dass Postgres nicht zulässt, dass Sie eine Where-Klausel basierend auf einer Spalte hinzufügen, die ein Alias für eine gecastete String-Spalte ist?

Answer 1

Haben Sie versucht, Ihre WHERE Klausel:

WHERE cast(foo.valueTS AS TIMESTAMP) <= now();

Haben Sie auch versucht, mit CURRENT_TIMESTAMP anstelle von now() ?

Ich weiß nicht, woran es sonst liegen könnte, es sei denn, es gibt andere Datenprobleme.

Answer 2

Ihr Problem ist, dass Sie ungültige Daten haben. Dies ist ein wichtiger Grund für die Verwendung geeigneter Datentypen, damit Sie Fehler bei der Eingabe abfangen können, anstatt sie später auszusortieren. Sie können etwa wie folgt vorgehen, um das Problem zu beheben:

UPDATE the_table
   SET value_ts = '1900-01-01 00:00:00'
 WHERE value_ts ~ '[ ]+';

Ich befürchte jedoch, dass Sie, wenn dies das Problem ist, noch mehr finden werden.