¿Cómo puedo convertir una cadena a integer y tener 0 en caso de error en el cast con PostgreSQL?

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)

Tenía el mismo tipo de necesidad y encontré que esto funcionaba 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 eliminar los caracteres no numéricos antes de la emisión.

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

Entonces los valores de text/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 de no dar 0 para todos casos, incluyendo un caso como "malo" (sin caracteres de dígitos en absoluto), hice esta declaració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 a convertir es solo caracteres no dígitos, como "malo":

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

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 produce NULL para valores NULOS 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 producirá el siguiente resultado:

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

Mientras que seleccionar solo values::integer dará lugar a un mensaje de error.

Espero que esto ayudar.

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

Nunca he trabajado con PostgreSQL pero he comprobado el manual para la sintaxis correcta de las sentencias IF en las consultas SELECT.

@La respuesta de Mateo 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 otra entrada de" sintaxis de entrada no vá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;

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 .

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

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

Volverá 123

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 que ayude

Si se supone que los datos son enteros, y solo necesita esos valores como enteros, ¿por qué no va toda la milla 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 los datos se insertan en la tabla.

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

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

Hace maravillas. Creo que funciona con IGUAL también.

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

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