Jak rzutować ciąg na liczbę całkowitą i mieć 0 w przypadku błędu w rzutowaniu z PostgreSQL?

W PostgreSQL mam tabelę z kolumną varchar. Dane mają być liczbami całkowitymi i potrzebuję ich w postaci liczby całkowitej w zapytaniu. Niektóre wartości są pustymi ciągami. Następujące:

SELECT myfield::integer FROM mytable

plony ERROR: invalid input syntax for integer: ""

Jak mogę zapytać o rzut i mieć 0 w przypadku błędu podczas rzutowania w postgres?

Po prostu zmagałem się z podobnym problemem, ale nie chciałem narzutu funkcji. Wymyśliłem następujące zapytanie:

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

Postgres skraca swoje warunki warunkowe, więc nie powinieneś otrzymywać żadnych niecałkowitych liczb uderzających w rzutowanie :: integer. Obsługuje również wartości NULL (nie będą one pasować do wyrażenia regularnego).

Jeśli chcesz zer zamiast wybierania, to instrukcja CASE powinna działać:

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

Można również utworzyć własną funkcję konwersji, wewnątrz którego można użyć bloków wyjątek:

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;

Testowanie:

=# 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)

Miałem taką samą potrzebę i stwierdziłem, że działa to dobrze dla mnie (postgres 8.4):

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

Niektóre przypadki testowe do zademonstrowania:

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"

Jeśli chcesz poradzić sobie z możliwością, że pole ma tekst nienumeryczny (na przykład „100bad”), możesz użyć regexp_replace, aby usunąć znaki nienumeryczne przed rzutowaniem.

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

Wtedy wartości text / varchar, takie jak „b3ad5”, również będą zawierać liczby

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

Aby rozwiązać obawę Chrisa Cogdona dotyczącą rozwiązania, które nie daje 0 dla wszystkich przypadków, w tym przypadku takiego jak „zły” (w ogóle bez znaków cyfrowych), poczyniłem to skorygowane stwierdzenie:

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

Działa podobnie do prostszych rozwiązań, z tą różnicą, że daje 0, gdy wartość do konwersji zawiera tylko znaki niecyfrowe, na przykład „zła”:

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

To może być trochę hack, ale w naszym przypadku wykonało to zadanie:

(0 || myfield)::integer

Wyjaśnienie (testowane na Postgres 8.4):

Wymienione powyżej rentowności wyrażenie NULLdla wartości null w myfieldi 0na pustych strunach (ta dokładna zachowanie może lub nie może zmieścić się na przypadek użycia).

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

Dane testowe:

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

Zapytanie da następujący wynik:

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

Natomiast tylko zaznaczenie values::integerspowoduje wyświetlenie komunikatu o błędzie.

Mam nadzieję że to pomoże.

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

Nigdy nie pracowałem z PostgreSQL, ale sprawdziłem w podręczniku poprawną składnię instrukcji IF w zapytaniach SELECT.

@ Odpowiedź Mateusza jest dobra. Ale może być prostsze i szybsze. A pytanie dotyczy konwersji pustych ciągów ( '') na 0inne dane wejściowe o „nieprawidłowej składni” lub „poza zakresem”, ale nie na inne:

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;

Zwraca to 0dla pustego ciągu i NULLwszelkich innych nieprawidłowych danych wejściowych.
Można go łatwo dostosować do dowolnej konwersji typu danych .

Wprowadzenie do bloku wyjątków jest znacznie droższe. Jeśli puste łańcuchy są powszechne, warto przechwycić ten przypadek przed zgłoszeniem wyjątku.
Jeśli puste łańcuchy są bardzo rzadkie, opłaca się przenieść test do klauzuli wyjątku.

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

Ta funkcja zawsze zwróci, 0jeśli w ciągu wejściowym nie ma cyfr.

SELECT parse_int('test12_3test');

wróci 123

Poniższy kod jest łatwy i działa. Oryginalna odpowiedź jest tutaj https://www.postgresql.org/message-id/371F1510.F86C876B@sferacarta.com

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)

mam nadzieję, że to pomoże

SUBSTRING może pomóc w niektórych przypadkach, możesz ograniczyć rozmiar int.

SELECT CAST(SUBSTRING('X12312333333333', '([\d]{1,9})') AS integer);

Jeśli dane mają być liczbami całkowitymi, a potrzebujesz tylko tych wartości jako liczb całkowitych, dlaczego nie przejdziesz przez całą milę i nie zamienisz kolumny na kolumnę całkowitą?

Następnie możesz wykonać tę konwersję niedozwolonych wartości na zera tylko raz, w punkcie systemu, w którym dane są wstawiane do tabeli.

Dzięki powyższej konwersji zmuszasz Postgres do wielokrotnego konwertowania tych wartości dla każdego pojedynczego wiersza w każdym zapytaniu dla tej tabeli - może to poważnie obniżyć wydajność, jeśli wykonasz wiele zapytań w tej kolumnie w tej tabeli.

Następująca funkcja działa

• użyj wartości domyślnej ( error_result) dla wyników, których nie można rzutować, np. abclub999999999999999999999999999999999999999999
• zachowuje nulljaknull
• usuwa spacje i inne białe znaki na wejściu
• wartości uznane za prawidłowe bigintssą porównywane z lower_boundnp. wymuszaniem tylko wartości dodatnich

CREATE OR REPLACE FUNCTION cast_to_bigint(text) 
RETURNS BIGINT AS $$
DECLARE big_int_value BIGINT DEFAULT NULL;
DECLARE error_result  BIGINT DEFAULT -1;
DECLARE lower_bound   BIGINT DEFAULT 0;
BEGIN
    BEGIN
        big_int_value := CASE WHEN $1 IS NOT NULL THEN GREATEST(TRIM($1)::BIGINT, lower_bound) END;
    EXCEPTION WHEN OTHERS THEN
        big_int_value := error_result;
    END;
RETURN big_int_value;
END;

Też mam tę samą potrzebę, ale to działa z JPA 2.0 i Hibernate 5.0.2:

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

Działa cuda. Myślę, że działa też z LIKE.

Powinno to również załatwić sprawę, ale dotyczy to SQL, a nie Postgres.

select avg(cast(mynumber as numeric)) from my table