Datenqualität in SQL Server // TRY_CONVERT für float und real sicher anwenden

Wer einmal eine Messreihe importiert hat, in der jede leere Zelle als 0 in der Zieltabelle landete, kennt die Falle: TRY_CONVERT(float, '') liefert nicht NULL, sondern 0. Der Mittelwert über die Spalte ist danach falsch, und dem Ergebnis sieht man das nicht an.

Auf einen Blick:

• real (= float(24), binary32, 4 Byte) und float (= float(53), binary64, 8 Byte) sind Näherungstypen. Sie speichern binär statt dezimal, weshalb 2 + 3.4 - 3.4 - 2 als float nicht 0 ergibt, sondern einen Rest von rund 4.44E-16.
• Bei der Konvertierung eines Textes nach float gilt der Punkt als Dezimaltrennzeichen. Ein Komma ergibt NULL, und zwar unabhängig von jeder Locale-Einstellung. Für andere Zieltypen sieht das anders aus, money etwa schluckt Kommas.
• Die leere Zeichenfolge ist der gefährliche Sonderfall: Sie wird zu 0 statt zu NULL. Ein CASE/TRIM-Vorschritt macht daraus den semantisch richtigen Leerwert.
• Postgres bildet dieselben Typen als double precision und real ab, beide nach IEEE 754 und damit mit demselben Rundungsverhalten. Bei ungültigem Text bricht Postgres allerdings mit einer Exception ab. Das NULL-Verhalten liefert ein PL/pgSQL-Wrapper fn_try_cast_double nach.

Voraussetzung: TRY_CONVERT existiert seit SQL Server 2012. Das sichere Pattern weiter unten nutzt TRIM und braucht daher SQL Server 2017 oder neuer, davor LTRIM(RTRIM(…)). Die Postgres-Beispiele setzen keine bestimmte Version voraus, mit einer Ausnahme: pg_input_is_valid kam mit Postgres 16. Alle Beispiele laufen ohne Beispieldatenbank.

Inhalt

• Float und real — zwei Näherungstypen
• Präzision und Speicherbedarf
• Text nach float konvertieren
• Typisierte Zahlen nach float konvertieren
• Decimal vs. Float
• Sichere Typ-Konvertierung
• Postgres-Brücke
• Zusammenfassung
• FAQ
• Verwandte Artikel

Float und real — zwei Näherungstypen

SQL Server stellt für die Speicherung von Gleitkommazahlen zwei Datentypen zur Verfügung: float(n) und real. Beide sind keine präzisen Datentypen. Sie legen einen Wert als binäre Mantisse mit Exponent ab. Damit lassen sich große Wertebereiche mit wenig Speicher darstellen, allerdings auf Kosten der Genauigkeit. Eine float-Variable kann augenscheinlich den Wert 0 halten und in einer weniger signifikanten Stelle trotzdem einen Rest ungleich 0 mitführen. Typische Anwendung sind technische Messwerte, Sensordaten und naturwissenschaftliche Berechnungen, also überall dort, wo eine Genauigkeit von 7 bis 15 signifikanten Stellen ausreicht.

Die Microsoft-Dokumentation zu TRY_CONVERT und zu CAST und CONVERT hält fest, dass der zu konvertierende Wert ein beliebiger Ausdruck sein darf. Damit kommen sowohl nvarchar-Texte aus CSV-, JSON- und XML-Importen als Eingangswert in Frage als auch bereits typisierte Zahlen, etwa ein decimal(18, 5) aus einer Pipeline-Berechnung. Die beiden Fälle verhalten sich unterschiedlich genug, um sie getrennt zu betrachten. Danach folgt das sichere Konvertierungs-Pattern.

Präzision und Speicherbedarf

Vor der Konvertierung steht die Typ-Wahl. Die zwei Gleitkomma-Typen unterscheiden sich durch die Anzahl der Bits, die für die Mantisse reserviert werden, und damit durch Speicherbedarf und Genauigkeit:

Der Parameter n in float(n) nimmt Werte von 1 bis 53 an, kennt intern aber nur zwei Stufen. Für n von 1 bis 24 reserviert SQL Server 4 Byte und speichert den Wert in Single Precision, exakt wie real. Für n von 25 bis 53 sind es 8 Byte in Double Precision, exakt wie float ohne Parameter. Fehlt die Angabe von n, nimmt SQL Server den Wert 53 an.

Konkret: SQL Server normalisiert n intern auf eine der beiden Stufen. Werte von 1 bis 24 landen auf float(24) und damit auf derselben Repräsentation wie real, Werte von 25 bis 53 auf float(53). Eine Deklaration wie float(30) erzeugt also keine eigene Zwischengröße.

Dieser Artikel arbeitet überwiegend mit float ohne Parameter, also mit float(53). Die Unterschiede zu real betreffen nur Präzision und Speicherbedarf, nicht die Konvertierungs-Regeln.

Text nach float konvertieren

Wird ein Wert vom Typ nvarchar oder varchar an TRY_CONVERT übergeben, muss der Text eine Zahl darstellen. Auf diesem Weg von Text nach float erwartet SQL Server den Punkt als Dezimaltrennzeichen und versteht weder ein Komma noch ein Tausendertrennzeichen. Das ist eine Eigenschaft dieses Konvertierungspfads, nicht von TRY_CONVERT als Funktion — bei money und smallmoney werden Kommas sogar stillschweigend geschluckt. Wissenschaftliche Notation ist dagegen erlaubt. Die leere Zeichenfolge nimmt eine Sonderrolle ein, auf die der Abschluss dieser Sektion zurückkommt.

  1: SELECT TRY_CONVERT(float, NULL        )  -- NULL
  2: SELECT TRY_CONVERT(float, N'123'      )  -- 123
  3: SELECT TRY_CONVERT(float, N'123,456'  )  -- NULL
  4: SELECT TRY_CONVERT(float, N'123.456'  )  -- 123.456
  5: SELECT TRY_CONVERT(float, N''         )  -- 0
  6: SELECT TRY_CONVERT(float, N' '        )  -- 0
  7: SELECT TRY_CONVERT(float, N'  123.456')  -- 123.456
  8: SELECT TRY_CONVERT(float, N'123.456  ')  -- 123.456
  9: SELECT TRY_CONVERT(float, N'123456E-3')  -- 123.456
 10: SELECT TRY_CONVERT(float, NCHAR(9)    )  -- NULL
 11: SELECT TRY_CONVERT(float, N'1e400'    )  -- NULL
 12: SELECT TRY_CONVERT(float, N'Infinity' )  -- NULL

  1: SELECT TRY_CONVERT(float, NULL        )  -- NULL
  2: SELECT TRY_CONVERT(float, N'123'      )  -- 123
  3: SELECT TRY_CONVERT(float, N'123,456'  )  -- NULL
  4: SELECT TRY_CONVERT(float, N'123.456'  )  -- 123.456
  5: SELECT TRY_CONVERT(float, N''         )  -- 0
  6: SELECT TRY_CONVERT(float, N' '        )  -- 0
  7: SELECT TRY_CONVERT(float, N'  123.456')  -- 123.456
  8: SELECT TRY_CONVERT(float, N'123.456  ')  -- 123.456
  9: SELECT TRY_CONVERT(float, N'123456E-3')  -- 123.456
 10: SELECT TRY_CONVERT(float, NCHAR(9)    )  -- NULL
 11: SELECT TRY_CONVERT(float, N'1e400'    )  -- NULL
 12: SELECT TRY_CONVERT(float, N'Infinity' )  -- NULL

Die Zeilen zeigen im Einzelnen:

• Zeile 3: '123,456' mit Komma ergibt NULL. Das Komma wird nicht als Dezimaltrennzeichen erkannt, und weder SET LANGUAGE noch SET DATEFORMAT ändern daran etwas. Wer aus einer CSV in deutscher Notation importiert, muss vor dem Aufruf normalisieren.
• Zeilen 5, 6: die leere Zeichenfolge und eine Zeichenfolge aus reinen Leerzeichen werden zu 0. Bei decimal und numeric wäre das Ergebnis an derselben Stelle NULL.
• Zeilen 7, 8: führende und folgende Leerzeichen verhindern die Konvertierung nicht.
• Zeile 9: wissenschaftliche Notation funktioniert bereits als Text. Bei der direkten Konvertierung eines Textes nach decimal ist sie dagegen nicht zulässig, TRY_CONVERT(decimal(5, 2), N'123456E-3') liefert NULL. Als bereits typisierte Zahl übergeben kommt decimal damit sehr wohl zurecht.
• Zeile 10: ein reines Tabulatorzeichen ergibt NULL und nicht 0. Die 0 gilt nur für echte Leerzeichen. Ein geschütztes Leerzeichen verhält sich wie der Tabulator.
• Zeilen 11, 12: ein Wert jenseits des float-Wertebereichs ergibt NULL, und die Schreibweisen 'Infinity' und 'NaN' kennt SQL Server nicht. Postgres akzeptiert beide, siehe Postgres-Brücke.

Kernaussage: Von den beiden Überraschungen ist die zweite die gefährlichere. Ein NULL bei Komma-Eingabe fällt in der Zieltabelle auf und landet in der Fehlerbehandlung. Die 0 aus einer leeren Zeichenfolge fällt nicht auf. Sie ist ein gültiger Messwert, sie übersteht jede NOT NULL-Prüfung, und sie verschiebt jeden Mittelwert über die Spalte. Ein leeres Feld in einer CSV bedeutet inhaltlich „unbekannt“ und nicht „null Einheiten“.

Typisierte Zahlen nach float konvertieren

Liegt der Eingangswert bereits typisiert vor, also als Ganzzahl, als Dezimalzahl oder in wissenschaftlicher Notation, reduzieren sich die Fälle deutlich:

  1: SELECT TRY_CONVERT(float, NULL     )  -- NULL
  2: SELECT TRY_CONVERT(float, 123      )  -- 123
  3: SELECT TRY_CONVERT(float, 123, 456 )  -- 123
  4: SELECT TRY_CONVERT(float, 123.456  )  -- 123.456
  5: SELECT TRY_CONVERT(float, 123456E-3)  -- 123.456

  1: SELECT TRY_CONVERT(float, NULL     )  -- NULL
  2: SELECT TRY_CONVERT(float, 123      )  -- 123
  3: SELECT TRY_CONVERT(float, 123, 456 )  -- 123
  4: SELECT TRY_CONVERT(float, 123.456  )  -- 123.456
  5: SELECT TRY_CONVERT(float, 123456E-3)  -- 123.456

Die Zeilen zeigen im Einzelnen:

• Zeile 3: hier steht kein Dezimaltrennzeichen, sondern der T-SQL-Parameter-Separator. TRY_CONVERT ist als Drei-Argument-Funktion (data_type, expression, style) definiert, der Parser liest die 456 also als style, und für float bleibt dieser Parameter wirkungslos. Zurück kommt der erste Ausdruck, also 123 — ein plausibel aussehender Wert ohne die Nachkommastellen. Im Text-Pfad wäre dasselbe Komma sichtbar an einem NULL gescheitert.
• Zeile 4: das Dezimal-Literal 123.456 ist selbst gar keine Gleitkommazahl. T-SQL typisiert es als numeric(6, 3), und erst die Konvertierung macht daraus einen float-Wert.
• Zeile 5: ein Literal mit E-Exponent ist dagegen von vornherein ein float-Literal. Die Konvertierung übernimmt den Wert unverändert.

Decimal vs. Float

decimal ist im Unterschied zu float und real ein präziser Datentyp. Er speichert Werte als vorzeichenbehaftete Folge von Dezimalziffern und stellt sie innerhalb der deklarierten Precision und Scale dezimal exakt dar (decimal(precision, scale)). Eine Rechnung wie 2 + 3.4 - 3.4 - 2 liefert damit verlässlich 0. float speichert dagegen binär mit fester Mantissen-Länge. Viele Dezimalzahlen, die in der menschlichen Schreibweise glatt wirken (0.1, 0.2, 3.4), sind in dieser Binärdarstellung nicht exakt abbildbar. Operationen sammeln deshalb Rundungsfehler in der Größenordnung der Mantissen-Auflösung ein.

Die gleiche Rechnung einmal als float, einmal als decimal:

  1: DECLARE @f1 AS float = 2;
  2: DECLARE @f2 AS float = 3.4;
  3: DECLARE @f3 AS float = @f1 + @f2;
  4: 
  5: SELECT @f3 - @f2 - @f1                  -- 4.44089209850063E-16
  6: 
  7: DECLARE @d1 AS decimal(2, 1) = 2;
  8: DECLARE @d2 AS decimal(2, 1) = 3.4;
  9: DECLARE @d3 AS decimal(2, 1) = @d1 + @d2;
 10: 
 11: SELECT @d3 - @d2 - @d1                  -- 0.0

  1: DECLARE @f1 AS float = 2;
  2: DECLARE @f2 AS float = 3.4;
  3: DECLARE @f3 AS float = @f1 + @f2;
  4: 
  5: SELECT @f3 - @f2 - @f1                  -- 4.44089209850063E-16
  6: 
  7: DECLARE @d1 AS decimal(2, 1) = 2;
  8: DECLARE @d2 AS decimal(2, 1) = 3.4;
  9: DECLARE @d3 AS decimal(2, 1) = @d1 + @d2;
 10: 
 11: SELECT @d3 - @d2 - @d1                  -- 0.0

Im float-Pfad bleibt nach der Subtraktion ein Rest von rund 4.44E-16 stehen, ein typischer Rundungsfehler innerhalb der 53-Bit-Mantisse von float im binary64-Format nach IEEE 754. Der Wert ist nicht die Auflösungs-Grenze des Datentyps, sondern das Ergebnis der drei beteiligten Operationen. Derselbe Rechenweg auf demselben Server liefert reproduzierbar genau diesen Rest. Im decimal-Pfad ist das Ergebnis exakt 0.0, weil keine Binär-Approximation stattfindet.

Dieselbe Rechnung mit real ergibt dagegen exakt 0. Der Darstellungsfehler ist dort sogar größer als bei float, denn 3.4 landet als real auf 3.4000000953674316 und als float auf 3.3999999999999999. In dieser konkreten Folge von Additionen und Subtraktionen hebt er sich vollständig auf. Wer nur diese eine Rechnung prüft, hielte real fälschlich für den genaueren Typ. Rundungsfehler sind eine Eigenschaft des Rechenwegs und keine Konstante des Datentyps.

Wichtig ist, wann diese Entscheidung fällt. Ein nachträglicher Cast repariert nichts, weil der float-Wert die Approximation bereits mitbringt:

  1: SELECT CAST(CAST(3.4 AS float) AS decimal(20, 17))   -- 3.39999999999999991
  2: SELECT CAST(3.4 AS decimal(20, 17))                  -- 3.40000000000000000

  1: SELECT CAST(CAST(3.4 AS float) AS decimal(20, 17))   -- 3.39999999999999991
  2: SELECT CAST(3.4 AS decimal(20, 17))                  -- 3.40000000000000000

Zeile 1 liefert nur eine dezimale Schreibweise des schon genäherten Werts. Die Abweichung in der letzten Stelle ist nicht mehr wegzurechnen.

Werte, die exakt dargestellt werden müssen, gehören deshalb schon beim Import in eine decimal(p, s)-Spalte: Geldbeträge, regulierte Reporting-Werte und dezimal definierte Mengen wie Gewichte oder Volumina. Reine Zählwerte gehören dagegen in einen Integer-Typ. Wer sie erst als float ablegt und später castet, hat den Informationsverlust zu diesem Zeitpunkt bereits eingetreten lassen. float und real gehören zu Werten, die ohnehin eine Messtoleranz mitbringen. Umgekehrt ist auch decimal nicht bedingungslos exakt, denn eine Konvertierung auf eine kleinere Scale rundet still, und arithmetische Operationen folgen eigenen Precision-Regeln. Beides führt Datenqualität in SQL Server // TRY_CONVERT für decimal und numeric sicher anwenden aus.

Sichere Typ-Konvertierung

„Sicher“ meint in dieser Serie: fehlertolerant, also kein Abbruch und NULL statt Laufzeitfehler. Es meint nicht automatisch verlustfrei. Bei float kommt eine zweite Einschränkung dazu, denn der Zieltyp ist eine Näherung und das Ergebnis damit von Haus aus nicht exakt.

Die Sektionen oben haben zwei Sonderfälle gezeigt, die im Import-Pfad ausdrücklich behandelt werden müssen:

• Leere Zeichenfolge: Sie wird zu 0 konvertiert. Wenn das die falsche Semantik ist, und bei CSV-Importen ist es das im Regelfall, muss sie vor dem TRY_CONVERT auf NULL abgebildet werden.
• Komma als Dezimaltrennzeichen: Es führt zu NULL. Liefert die Quelle deutsche Notation ('123,45678'), muss das Komma vor dem TRY_CONVERT durch einen Punkt ersetzt werden.

Das folgende Beispiel löst beide Sonderfälle gemeinsam:

  1: DECLARE @p_input AS nvarchar(30);
  2: SET @p_input = N'123,45678';
  3: 
  4: SELECT TRY_CONVERT( float
  5:                   , REPLACE( CASE WHEN TRIM(@p_input) = ''
  6:                                      THEN NULL
  7:                                      ELSE @p_input
  8:                                 END
  9:                            , ','
 10:                            , '.'
 11:                            )
 12:                   ) AS [Output];  -- 123.45678

  1: DECLARE @p_input AS nvarchar(30);
  2: SET @p_input = N'123,45678';
  3: 
  4: SELECT TRY_CONVERT( float
  5:                   , REPLACE( CASE WHEN TRIM(@p_input) = ''
  6:                                      THEN NULL
  7:                                      ELSE @p_input
  8:                                 END
  9:                            , ','
 10:                            , '.'
 11:                            )
 12:                   ) AS [Output];  -- 123.45678

Vier Einschränkungen gehören zu diesem Pattern dazu:

• Bekannte Quellnotation: REPLACE(',', '.') ist nur dann richtig, wenn für diese Quelle feststeht, dass das Komma das Dezimaltrennzeichen ist. '1,234' ist für sich genommen mehrdeutig — in deutscher Lesart 1.234, in amerikanischer 1234, also Faktor 1000 auseinander, und beide Wege liefern klaglos einen Wert. Bei gemischten oder unbekannten Notationen gehört die Notation erst bestimmt oder der Satz als Datenqualitätsfehler zurückgewiesen, bevor irgendetwas ersetzt wird.
• Eingabegrammatik: Das Pattern normalisiert ausschließlich den Dezimaltrenner. Ein Tausendertrennzeichen bleibt stehen, aus '1.234,56' wird dadurch '1.234.56' und am Ende NULL. Solche Eingaben muss eine vorgelagerte Bereinigung auflösen. Beim Leerraum ist das Pattern dagegen unkritisch: TRIM entfernt ohne weitere Angabe nur normale Leerzeichen, und genau die sind der Fall, der sonst zur 0 würde. Tabulatoren und geschützte Leerzeichen konvertieren ohnehin zu NULL.
• Wertebereich: Ein Wert jenseits von 1.79E+308 ergibt NULL, bei real beginnt diese Grenze schon bei 3.4E+38. Ein NULL im Ziel-Feld hat damit drei mögliche Ursachen: eine ungültige Zahl-Darstellung, einen Wert außerhalb des Wertebereichs oder ein echtes NULL in der Quelle. Für die Fehlerdiagnose lohnt es sich, den Rohwert mitzuspeichern.
• Präzisionsgrenze: float(53) hat eine binäre Präzision von 53 Bit, was ungefähr 15 signifikanten Dezimalziffern entspricht. Die gelegentlich genannten 17 Stellen meinen etwas anderes, nämlich wie viele Dezimalstellen nötig sind, um einen binary64-Wert verlustfrei als Text zu schreiben und wieder einzulesen. Für die Konvertierung zählt die erste Zahl. Das Pattern macht sie fehlertolerant, aber nicht exakt, und wo Exaktheit zählt, ist decimal(p, s) der richtige Zieltyp.

Wer das Pattern in einem ETL-Prozess für mehrere Spalten braucht, abstrahiert es zu einer benutzerdefinierten Funktion fn_try_convert_float(@p_input nvarchar). Den Bauplan dafür liefert Design Pattern // Sichere Typ-Konvertierung mit T-SQL.

Postgres-Brücke

Bei Gleitkommazahlen ist der Wechsel zwischen den Engines unkritischer als bei jedem anderen Typ dieser Serie. Beide setzen float(53) beziehungsweise double precision auf das IEEE-754-Format binary64 auf, Postgres laut Dokumentation auf allen aktuell unterstützten Plattformen. Derselbe endliche Wert liegt deshalb in beiden identisch im Speicher, und die Vergleichsrechnung aus der Sektion oben kommt in Postgres auf genau denselben Rest. Eine allgemeine Portabilitäts-Garantie ist das trotzdem nicht: Parsing, Zwischenergebnisse, Optimierung und Ausgabe bleiben Sache der jeweiligen Engine.

Beim Konvertierungs-Verhalten gibt es drei Unterschiede:

• Kein Try-Pendant: CAST(s AS double precision) ist der Postgres-Weg, und er wirft bei ungültigem Text eine Exception. Ein direktes Gegenstück zu TRY_CONVERT gibt es weiterhin nicht, weder als eingebaute try_cast-Funktion noch als CAST … ON ERROR-Syntax, zuletzt geprüft an Postgres 18. Das NULL-Verhalten schreibt man also selbst.
• Leere Zeichenfolge: Postgres bricht dort ab, wo SQL Server zu 0 konvertiert. ''::double precision meldet invalid input syntax mit dem SQLSTATE 22P02. Das ist die freundlichere Variante, weil der Fehler sichtbar wird statt sich als Messwert zu tarnen.
• Infinity und NaN: Postgres kennt beide als gültige Eingaben und speichert sie als Wert. SQL Server lehnt sie mit NULL ab. Wer Messreihen zwischen den Engines bewegt, fängt diese beiden Schreibweisen besser vor dem Transfer ab.

Der Wrapper bildet nicht TRY_CONVERT allein nach, sondern gleich das sichere Pattern aus der Sektion oben: leere Zeichenfolge zu NULL, Komma zu Punkt, Fehler zu NULL.

  1: CREATE OR REPLACE FUNCTION fn_try_cast_double
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS double precision
  6: LANGUAGE plpgsql
  7: IMMUTABLE
  8: AS $function$
  9: BEGIN
 10: 
 11:    IF p_input IS NULL OR TRIM(p_input) = '' THEN
 12:       RETURN NULL;
 13:    END IF;
 14: 
 15:    RETURN CAST(REPLACE(p_input, ',', '.') AS double precision);
 16: 
 17: EXCEPTION
 18:    WHEN invalid_text_representation OR numeric_value_out_of_range THEN
 19:       RETURN NULL;
 20: END;
 21: $function$;
 22: 
 23: SELECT fn_try_cast_double('123.456');     -- 123.456
 24: SELECT fn_try_cast_double('123,456');     -- 123.456
 25: SELECT fn_try_cast_double('123456E-3');   -- 123.456
 26: SELECT fn_try_cast_double('');            -- NULL
 27: SELECT fn_try_cast_double('1e400');       -- NULL

  1: CREATE OR REPLACE FUNCTION fn_try_cast_double
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS double precision
  6: LANGUAGE plpgsql
  7: IMMUTABLE
  8: AS $function$
  9: BEGIN
 10: 
 11:    IF p_input IS NULL OR TRIM(p_input) = '' THEN
 12:       RETURN NULL;
 13:    END IF;
 14: 
 15:    RETURN CAST(REPLACE(p_input, ',', '.') AS double precision);
 16: 
 17: EXCEPTION
 18:    WHEN invalid_text_representation OR numeric_value_out_of_range THEN
 19:       RETURN NULL;
 20: END;
 21: $function$;
 22: 
 23: SELECT fn_try_cast_double('123.456');     -- 123.456
 24: SELECT fn_try_cast_double('123,456');     -- 123.456
 25: SELECT fn_try_cast_double('123456E-3');   -- 123.456
 26: SELECT fn_try_cast_double('');            -- NULL
 27: SELECT fn_try_cast_double('1e400');       -- NULL

Der EXCEPTION-Block fängt gezielt die zwei erwartbaren Fehlerklassen ab, die ungültige Zahl-Darstellung (invalid_text_representation, SQLSTATE 22P02) und den Wertebereichs-Überlauf (numeric_value_out_of_range, SQLSTATE 22003). Ein pauschales WHEN OTHERS würde auch unerwartete Fehler still zu NULL machen. Für real sieht der Wrapper identisch aus, nur mit RETURNS real.

Laut Postgres-Dokumentation ist ein Block mit EXCEPTION-Teil allerdings deutlich teurer als einer ohne. Seit Postgres 16 gibt es dafür eine set-basierte Alternative: pg_input_is_valid prüft eine Eingabe gegen einen Zieltyp, pg_input_error_info liefert Meldung und SQLSTATE dazu. Beide prüfen allerdings nur die Typ-Gültigkeit, die Normalisierung mit TRIM und REPLACE bleibt vorgelagert.

  1: SELECT pg_input_is_valid('123.456' , 'double precision');   -- true
  2: SELECT pg_input_is_valid('123,456' , 'double precision');   -- false
  3: SELECT pg_input_is_valid('1e400'   , 'double precision');   -- false
  4: SELECT pg_input_is_valid('Infinity', 'double precision');   -- true
  5: 
  6: SELECT
  7:     sql_error_code
  8:    ,message
  9: FROM
 10:    pg_input_error_info('1e400', 'double precision');
 11: -- 22003 | "1e400" is out of range for type double precision
 

  1: SELECT pg_input_is_valid('123.456' , 'double precision');   -- true
  2: SELECT pg_input_is_valid('123,456' , 'double precision');   -- false
  3: SELECT pg_input_is_valid('1e400'   , 'double precision');   -- false
  4: SELECT pg_input_is_valid('Infinity', 'double precision');   -- true
  5: 
  6: SELECT
  7:     sql_error_code
  8:    ,message
  9: FROM
 10:    pg_input_error_info('1e400', 'double precision');
 11: -- 22003 | "1e400" is out of range for type double precision
 

Wenn die Eingabe formatierte Zahlen mit Tausender- und Dezimaltrennzeichen liefert, greift to_number mit einem Format-Pattern. Der Aufruf ist über die Session-Variable lc_numeric an die Locale gebunden:

  1: SET lc_numeric = 'de_DE.utf8';
  2: SELECT to_number('1.234,56', 'FM999G999D99');   -- 1234.56
  3: SELECT to_number('1,234.56', 'FM999G999D99');   -- 1.23
  4: 
  5: SET lc_numeric = 'en_US.utf8';
  6: SELECT to_number('1.234,56', 'FM999G999D99');   -- 1.23
  7: SELECT to_number('1,234.56', 'FM999G999D99');   -- 1234.56
  8: 
  9: -- Literale Formatzeichen bleiben von der Locale unberuehrt:
 10: SELECT to_number('1,234.56', 'FM999,999.99');   -- 1234.56 unter beiden Locales

  1: SET lc_numeric = 'de_DE.utf8';
  2: SELECT to_number('1.234,56', 'FM999G999D99');   -- 1234.56
  3: SELECT to_number('1,234.56', 'FM999G999D99');   -- 1.23
  4: 
  5: SET lc_numeric = 'en_US.utf8';
  6: SELECT to_number('1.234,56', 'FM999G999D99');   -- 1.23
  7: SELECT to_number('1,234.56', 'FM999G999D99');   -- 1234.56
  8: 
  9: -- Literale Formatzeichen bleiben von der Locale unberuehrt:
 10: SELECT to_number('1,234.56', 'FM999,999.99');   -- 1234.56 unter beiden Locales

Noch wichtiger als die Locale ist, was to_number nicht tut: Zeichen, die im Format-Muster nicht vorgesehen sind, überspringt der Parser stillschweigend.

  1: SELECT to_number('1.234,56abc' , 'FM999G999D99');   -- 1.23
  2: SELECT to_number('EUR 1.234,56', 'FM999G999D99');   -- 1.23
  3: SELECT to_number('12ab34'      , 'FM999G999D99');   -- 1234

  1: SELECT to_number('1.234,56abc' , 'FM999G999D99');   -- 1.23
  2: SELECT to_number('EUR 1.234,56', 'FM999G999D99');   -- 1.23
  3: SELECT to_number('12ab34'      , 'FM999G999D99');   -- 1234

Zeile 3 ist der Extremfall: Aus einer offensichtlich kaputten Eingabe wird eine plausible Zahl. to_number ist ein Formatierungs-Parser und kein Validator. Für eine Datenqualitäts-Prüfung gehört die Eingabe vorher gegen das erwartete Format geprüft, etwa mit pg_input_is_valid.

Kernaussage: to_number meldet in beiden Fällen keinen Fehler, sondern liefert einen stillen Falschwert — bei unpassender Locale ebenso wie bei kaputter Eingabe (beides nachgestellt unter PostgreSQL 17). Deshalb gehört das Eingabeformat im ETL-Prozess explizit festgeschrieben: G und D ziehen den Trenner aus lc_numeric, ein . oder , im Format-String ist dagegen ein literales Zeichen und locale-unabhängig, wie Zeile 10 oben zeigt. Wer die Notation der Quelle kennt, schreibt sie damit fest. Ein Detail für den Rückweg: to_number liefert numeric, für double precision gehört ein Cast dazu.

Typ-Mapping T-SQL zu Postgres:

Zusammenfassung

• real (= float(24), binary32, 4 Byte) und float (= float(53), binary64, 8 Byte) sind Näherungstypen, und der Parameter n kennt nur diese beiden Stufen.
• Aus Text ergibt ein Komma NULL, eine leere Zeichenfolge 0 und wissenschaftliche Notation den erwarteten Wert. Nur die 0 kommt ohne Warnsignal, also braucht sie den CASE/TRIM-Vorschritt.
• Als Literal im Quelltext geschrieben wird 123, 456 zum style-Parameter und liefert 123. Das fängt kein Pattern ab, sondern nur Code-Disziplin.
• Rundungsfehler sind eine Eigenschaft des Rechenwegs. Wer exakte Dezimalwerte braucht, entscheidet das beim Schema-Entwurf, denn ein späteres CAST gespeicherter float-Werte holt die Approximation nicht zurück.
• Postgres nutzt dasselbe IEEE-754-Format und speichert denselben endlichen Wert gleich, bricht bei ungültigem Text aber ab. Dafür gibt es den Wrapper fn_try_cast_double, ab Postgres 16 die Vorprüfung pg_input_is_valid und für formatierte Zahlen to_number mit explizit festgeschriebenem Format.

Für die Praxis als Entscheidungshilfe:

FAQ

Verwandte Artikel

ETL-Kontext:

• Datenqualität in einem ETL-Prozess
• ETL vs. ELT — woran du erkennst, welches Muster du wirklich gebaut hast — Makro-Einordnung: ETL vs. ELT als Architektur-Entscheidung, nicht als Reihenfolge der Buchstaben.

TRY_CONVERT für andere Datentypen:

• Datenqualität in SQL Server // TRY_CONVERT für date, datetime, datetime2 und time sicher anwenden
• Datenqualität in SQL Server // TRY_CONVERT für decimal und numeric sicher anwenden
• Datenqualität in SQL Server // TRY_CONVERT für bigint, int, smallint und tinyint sicher anwenden
• Datenqualität in SQL Server // TRY_CONVERT für money und smallmoney sicher anwenden
• Datenqualität in SQL Server // TRY_CONVERT für bit sicher anwenden — Ja/Nein-Werte konvertieren

Grundlagen:

• Datenqualität // Grundlagen der Typ-Konvertierung mit T-SQL — CAST, CONVERT, TRY_CAST und TRY_CONVERT im Vergleich.