tipo - to_char postgresql ejemplos

¿Cómo puedo convertir una cadena en un entero y tener 0 en caso de error en el molde con PostgreSQL?

Question

En PostgreSQL tengo una tabla con una columna varchar. Se supone que los datos son enteros y los necesito enteros en una consulta. Algunos valores son cadenas vacías. El seguimiento:

SELECT myfield::integer FROM mytable

produce ERROR: invalid input syntax for integer: ""

¿Cómo puedo consultar un lanzamiento y tener 0 en caso de error durante el lanzamiento en postgres?

Answer 1

Encontré el siguiente código fácil y funcional. La respuesta original está aquí 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)

Espero eso ayude

Answer 2

Estaba luchando con un problema similar yo mismo, pero no quería la sobrecarga de una función. Se me ocurrió la siguiente consulta:

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

Postgres ataca sus condicionales, por lo que no deberías obtener ningún entero que golpee tu conversión de enteros. También maneja valores NULL (no coincidirán con la expresión regular).

Si quiere ceros en lugar de no seleccionar, entonces una sentencia CASE debería funcionar:

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

Answer 3

Esto podría ser algo así como un truco, pero hizo el trabajo en nuestro caso:

(0 || myfield)::integer

Explicación (Probado en Postgres 8.4):

La expresión mencionada anteriormente proporciona NULL para NULL-values ​​en myfield y 0 para cadenas vacías (Este comportamiento exacto puede o no ajustarse a su caso de uso).

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

Datos de prueba:

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

La consulta arrojará el siguiente resultado:

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

Mientras que select only values::integer dará como resultado un mensaje de error.

Espero que esto ayude.

Answer 4

Esto también debería hacer el trabajo, pero esto es a través de SQL y no postgres específico.

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

Answer 5

Si se supone que los datos son enteros, y usted solo necesita esos valores como enteros, ¿por qué no recorre todo el kilómetro y convierte la columna en una columna entera?

Entonces podría hacer esta conversión de valores ilegales en ceros solo una vez, en el punto del sistema donde se insertan los datos en la tabla.

Con la conversión anterior, forzará a Postgres a convertir esos valores una y otra vez para cada fila en cada consulta para esa tabla; esto puede degradar seriamente el rendimiento si realiza muchas consultas en esta columna de esta tabla.

Answer 6

También puede crear su propia función de conversión, dentro de la cual puede usar bloques de excepción:

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;

Pruebas:

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

Answer 7

También tengo la misma necesidad, pero eso funciona con JPA 2.0 e Hibernate 5.0.2:

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

Funciona de maravilla Creo que funciona con LIKE también.

Answer 8

Tuve el mismo tipo de necesidad y encontré que esto funciona bien para mí (postgres 8.4):

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

Algunos casos de prueba para demostrar:

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"

Si necesita manejar la posibilidad de que el campo tenga texto no numérico (como "100bad"), puede usar regexp_replace para quitar los caracteres no numéricos antes del lanzamiento.

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

Luego, los valores de texto / varchar como "b3ad5" también darán números

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

Para abordar la preocupación de Chris Cogdon con la solución que no proporciona 0 en todos los casos, incluido un caso como "malo" (sin caracteres de dígitos), hice esta afirmación ajustada:

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

Funciona de manera similar a las soluciones más simples, excepto que dará 0 cuando el valor para convertir sea caracteres que no sean dígitos, como "malo":

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

Answer 9

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

No he trabajado nunca con PostgreSQL, pero revisé el manual para ver la sintaxis correcta de las declaraciones IF en las consultas SELECT.

Answer 10

@ La respuesta de Matthew es buena. Pero puede ser más simple y más rápido. Y la pregunta pide convertir cadenas vacías ( '''' ) a 0 , pero no a otras entradas de "entrada de entrada inválida" o "fuera de rango":

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;

Esto devuelve 0 para una cadena vacía y NULL para cualquier otra entrada no válida.
Se puede adaptar fácilmente para cualquier conversión de tipo de datos .

Entrar en un bloque de excepción es sustancialmente más caro. Si las cadenas vacías son comunes , tiene sentido detectar ese caso antes de generar una excepción.
Si las cadenas vacías son muy raras, vale la pena mover la prueba a la cláusula de excepción.

Answer 11

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

Esta función siempre devolverá 0 si no hay dígitos en la cadena de entrada.

SELECT parse_int(''test12_3test'');

regresará 123