wake-up-neo.net

Wie wandle ich eine Zeichenfolge in eine Ganzzahl um und habe im Falle eines Fehlers die Besetzung mit PostgreSQL?

In PostgreSQL habe ich eine Tabelle mit einer Varchar-Spalte. Die Daten sollen Integer sein und ich brauche sie in Integer-Typ in einer Abfrage. Einige Werte sind leere Zeichenfolgen. Die folgenden:

SELECT myfield::integer FROM mytable

ergibt ERROR: invalid input syntax for integer: ""

Wie kann ich eine Besetzung abfragen und im Fehlerfall während der Besetzung in Postgres 0 haben?

101
silviot

Ich habe gerade mit einem ähnlichen Problem gerungen, wollte aber nicht den Aufwand einer Funktion. Ich habe folgende Frage gestellt:

SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';

Postgres führt eine Abkürzung für die Bedingungseinstellungen ein. Sie sollten also keine Ganzzahlen erhalten, die Ihre Ganzzahl :: treffen. Es behandelt auch NULL-Werte (sie stimmen nicht mit dem regulären Ausdruck überein).

Wenn Sie Nullen möchten, statt keine Auswahl zu treffen, sollte eine CASE-Anweisung funktionieren:

SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;
131
Anthony Briggs

Sie können auch eine eigene Konvertierungsfunktion erstellen, in der Sie can Ausnahmeblöcke verwenden können:

CREATE OR REPLACE FUNCTION convert_to_integer(v_input text)
RETURNS INTEGER AS $$
DECLARE v_int_value INTEGER DEFAULT NULL;
BEGIN
    BEGIN
        v_int_value := v_input::INTEGER;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Invalid integer value: "%".  Returning NULL.', v_input;
        RETURN NULL;
    END;
RETURN v_int_value;
END;
$$ LANGUAGE plpgsql;

Testen:

=# select convert_to_integer('1234');
 convert_to_integer 
--------------------
               1234
(1 row)

=# select convert_to_integer('');
NOTICE:  Invalid integer value: "".  Returning NULL.
 convert_to_integer 
--------------------

(1 row)

=# select convert_to_integer('chicken');
NOTICE:  Invalid integer value: "chicken".  Returning NULL.
 convert_to_integer 
--------------------

(1 row)
78
Matthew Wood

Ich hatte das gleiche Bedürfnis und stellte fest, dass dies für mich gut war (postgres 8.4):

CAST((COALESCE(myfield,'0')) AS INTEGER)

Einige Testfälle zum Demonstrieren:

db=> select CAST((COALESCE(NULL,'0')) AS INTEGER);
 int4
------
    0
(1 row)

db=> select CAST((COALESCE('','0')) AS INTEGER);
 int4
------
    0
(1 row)

db=> select CAST((COALESCE('4','0')) AS INTEGER);
 int4
------
    4
(1 row)

db=> select CAST((COALESCE('bad','0')) AS INTEGER);
ERROR:  invalid input syntax for integer: "bad"

Wenn Sie mit der Möglichkeit umgehen müssen, dass das Feld nicht numerischen Text enthält (z. B. "100bad"), können Sie regexp_replace verwenden, um nicht numerische Zeichen vor der Umwandlung zu entfernen.

CAST(REGEXP_REPLACE(COALESCE(myfield,'0'), '[^0-9]+', '', 'g') AS INTEGER)

Dann geben Text/varchar-Werte wie "b3ad5" auch Zahlen aus

db=> select CAST(REGEXP_REPLACE(COALESCE('b3ad5','0'), '[^0-9]+', '', 'g') AS INTEGER);
 regexp_replace
----------------
             35
(1 row)

Um Chris Cogdons Besorgnis darüber anzusprechen, dass die Lösung nicht für alle Fälle 0 gibt, einschließlich eines Falles wie "schlecht" (überhaupt keine Ziffern), habe ich diese angepasste Aussage gemacht:

CAST((COALESCE(NULLIF(REGEXP_REPLACE(myfield, '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);

Es funktioniert ähnlich wie die einfacheren Lösungen, außer dass 0 ausgegeben wird, wenn der zu konvertierende Wert nur aus nicht numerischen Zeichen besteht, wie z. B. "bad":

db=> select CAST((COALESCE(NULLIF(REGEXP_REPLACE('no longer bad!', '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);
     coalesce
----------
        0
(1 row)
23
ghbarratt

Dies mag ein bisschen hacken, aber in unserem Fall wurde es erledigt:

(0 || myfield)::integer

Erklärung (getestet auf Postgres 8.4):

Der oben erwähnte Ausdruck liefert NULL für NULL-Werte in myfield und 0 für leere Zeichenfolgen (dieses genaue Verhalten kann in Ihren Anwendungsfall passen oder nicht.

SELECT id, (0 || values)::integer from test_table ORDER BY id

Testdaten:

CREATE TABLE test_table
(
  id integer NOT NULL,
  description character varying,
  "values" character varying,
  CONSTRAINT id PRIMARY KEY (id)
)

-- Insert Test Data
INSERT INTO test_table VALUES (1, 'null', NULL);
INSERT INTO test_table VALUES (2, 'empty string', '');
INSERT INTO test_table VALUES (3, 'one', '1');

Die Abfrage ergibt folgendes Ergebnis:

 ---------------------
 |1|null        |NULL|
 |2|empty string|0   |
 |3|one         |1   |
 ---------------------

Wenn Sie nur values::integer auswählen, wird eine Fehlernachricht ausgegeben.

Hoffe das hilft.

18
Matt

@ Matthews Antwort ist gut. Es kann jedoch einfacher und schneller sein. Die Frage fordert Sie auf, leere Zeichenfolgen ('') in 0 zu konvertieren, nicht aber andere "ungültige Eingabesyntax" oder "out of range" -Eingabe:

CREATE OR REPLACE FUNCTION convert_to_int(text)
  RETURNS int AS
$func$
BEGIN
   IF $1 = '' THEN  -- special case for empty string like requested
      RETURN 0;
   ELSE
      RETURN $1::int;
   END IF;

EXCEPTION WHEN OTHERS THEN
   RETURN NULL;  -- NULL for other invalid input

END
$func$  LANGUAGE plpgsql IMMUTABLE;

Dies gibt 0 für eine leere Zeichenfolge und NULL für alle anderen ungültigen Eingaben zurück.
Es kann leicht für any Datentypkonvertierung angepasst werden.

Die Eingabe eines Ausnahmeblocks ist wesentlich teurer. Wenn leere Zeichenfolgen common sind, ist es sinnvoll, diesen Fall abzufangen, bevor eine Ausnahme ausgelöst wird.
Wenn leere Zeichenfolgen sehr selten sind, lohnt es sich, den Test in die Ausnahmeklausel zu verschieben.

3

SELECT CASE WHEN myfield="" THEN 0 ELSE myfield::integer END FROM mytable

Ich habe noch nie mit PostgreSQL gearbeitet, aber ich habe das manual auf die korrekte Syntax von IF-Anweisungen in SELECT-Abfragen überprüft.

3
Jan Hančič
CREATE OR REPLACE FUNCTION parse_int(s TEXT) RETURNS INT AS $$
BEGIN
  RETURN regexp_replace(('0' || s), '[^\d]', '', 'g')::INT;
END;
$$ LANGUAGE plpgsql;

Diese Funktion gibt immer 0 Zurück, wenn die Eingabezeichenfolge keine Ziffern enthält.

SELECT parse_int('test12_3test');

wird zurückgeben 123

1
Oleg Mikhailov

Ich fand den folgenden Code einfach und funktionierend. Die ursprüngliche Antwort ist hier https://www.postgresql.org/message-id/[email protected]

prova=> create table test(t text, i integer);
CREATE

prova=> insert into test values('123',123);
INSERT 64579 1

prova=> select cast(i as text),cast(t as int)from test;
text|int4
----+----
123| 123
(1 row)

ich hoffe es hilft

1
Ashish Rana

Ich habe auch das gleiche Bedürfnis, aber das funktioniert mit JPA 2.0 und Hibernate 5.0.2:

SELECT p FROM MatchProfile p WHERE CONCAT(p.id, '') = :keyword

Wunder wirkt. Ich denke, es funktioniert auch mit LIKE.

0
Hendy Irawan

Wenn es sich bei den Daten um Ganzzahlen handeln soll und Sie nur diese Werte als Ganzzahlen benötigen, können Sie die Spalte in eine Ganzzahlspalte konvertieren.

Dann können Sie diese Umwandlung von ungültigen Werten in Nullen an der Stelle des Systems vornehmen, an der die Daten in die Tabelle eingefügt werden.

Mit der obigen Konvertierung zwingen Sie Postgres, diese Werte immer wieder für jede einzelne Zeile in jeder Abfrage für diese Tabelle zu konvertieren. Dies kann die Leistung stark beeinträchtigen, wenn Sie viele Abfragen für diese Spalte in dieser Tabelle ausführen.

0
Bandi-T