Comment puis-je convertir une chaîne en entier et avoir 0 en cas d’erreur dans la dissortingbution avec PostgreSQL?

Dans PostgreSQL, j’ai une table avec une colonne varchar. Les données sont supposées être des entiers et j’en ai besoin dans un type entier dans une requête. Certaines valeurs sont des chaînes vides. Le suivant:

SELECT myfield::integer FROM mytable 

renvoie ERROR: invalid input syntax for integer: ""

Comment puis-je interroger un casting et avoir 0 en cas d’erreur lors de la dissortingbution dans postgres?

Je me débattais simplement avec un problème similaire, mais je ne voulais pas que la fonction soit trop lourde. Je suis venu avec la requête suivante:

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

Postgres raccourcit ses conditions, donc vous ne devriez pas avoir de non-entiers qui frappent votre fonte :: integer. Il gère également les valeurs NULL (elles ne correspondront pas à l’expression rationnelle).

Si vous voulez des zéros au lieu de ne pas sélectionner, une instruction CASE devrait fonctionner:

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

Vous pouvez également créer votre propre fonction de conversion, à l’intérieur de laquelle vous pouvez utiliser des blocs d’exception:

 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; 

Essai:

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

J’avais le même genre de besoin et j’ai trouvé que cela fonctionnait bien pour moi (postgres 8.4):

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

Quelques cas de test pour démontrer:

 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 vous devez gérer la possibilité que le champ contienne du texte non numérique (tel que “100bad”), vous pouvez utiliser regexp_replace pour supprimer les caractères non numériques avant la dissortingbution.

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

Alors les valeurs text / varchar comme “b3ad5” donneront aussi des nombres

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

Pour répondre à l’inquiétude de Chris Cogdon, la solution ne donnant pas 0 pour tous les cas, y compris un cas tel que “mauvais” (aucun caractère numérique), j’ai fait cette déclaration ajustée:

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

Il fonctionne de manière similaire aux solutions les plus simples, sauf qu’il donnera 0 lorsque la valeur à convertir est constituée uniquement de caractères non numériques, tels que «mauvais»:

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

Cela pourrait être un peu un hack, mais cela a fait le travail dans notre cas:

 (0 || myfield)::integer 

Explication (Testé sur Postgres 8.4):

L’expression mentionnée ci-dessus donne NULL pour les valeurs NULL dans myfield et 0 pour les chaînes vides (Ce comportement exact peut ou non correspondre à votre cas d’utilisation).

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

Données de test:

 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 ssortingng', ''); INSERT INTO test_table VALUES (3, 'one', '1'); 

La requête donnera le résultat suivant:

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

Alors que select only values::integer entraînera un message d’erreur.

J’espère que cela t’aides.

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

Je n’ai jamais travaillé avec PostgreSQL mais j’ai vérifié le manuel pour la syntaxe correcte des instructions IF dans les requêtes SELECT.

@ La réponse de Matthew est bonne. Mais cela peut être plus simple et plus rapide. Et la question demande de convertir les chaînes vides ( '' ) en 0 , mais pas les autres entrées “syntaxe en entrée invalide” ou “en dehors de la plage”:

 CREATE OR REPLACE FUNCTION convert_to_int(text) RETURNS int AS $func$ BEGIN IF $1 = '' THEN -- special case for empty ssortingng 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; 

Cela renvoie 0 pour une chaîne vide et NULL pour toute autre entrée non valide.
Il peut facilement être adapté à toute conversion de type de données .

La saisie d’un bloc d’exception est nettement plus coûteuse. Si les chaînes vides sont courantes, il est logique de saisir ce cas avant de déclencher une exception.
Si les chaînes vides sont très rares, il vaut mieux déplacer le test vers la clause d’exception.

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

Cette fonction retournera toujours 0 s’il n’y a pas de chiffres dans la chaîne d’entrée.

SELECT parse_int('test12_3test');

reviendra 123

J’ai trouvé le code suivant facile et fonctionnel. La réponse originale est ici 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) 

J’espère que cela aide

Si les données sont supposées être des entiers, et que vous n’avez besoin que de ces valeurs comme nombres entiers, pourquoi ne pas parcourir tout le mile et convertir la colonne en une colonne entière?

Ensuite, vous pouvez effectuer cette conversion de valeurs illégales en zéros juste une fois, au point du système où les données sont insérées dans la table.

Avec la conversion ci-dessus, vous forcez Postgres à convertir ces valeurs à chaque fois pour chaque ligne de chaque requête. Cela peut sérieusement affecter les performances si vous interrogez cette colonne dans ce tableau.

J’ai aussi le même besoin mais cela fonctionne avec JPA 2.0 et Hibernate 5.0.2:

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

Fonctionne à merveille. Je pense que ça marche avec LIKE aussi.

Cela devrait également faire l’affaire, mais cela concerne SQL et non postgres.

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