Comment convertir une chaîne en entier et avoir 0 en cas d'erreur dans la conversion avec PostgreSQL ?

Question

Dans PostgreSQL, j'ai une table avec une colonne varchar. Les données sont censées être des entiers et j'en ai besoin dans le type entier dans une requête. Certaines valeurs sont des chaînes de caractères vides. Ce sont les suivantes :

SELECT myfield::integer FROM mytable

donne ERROR: invalid input syntax for integer: ""

Comment puis-je interroger un cast et avoir 0 en cas d'erreur pendant le cast dans postgres ?

Answer 1

Je me débattais moi-même avec un problème similaire, mais je ne voulais pas les frais généraux d'une fonction. Je suis arrivé à la requête suivante :

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

Postgres raccourcit ses conditionnels, de sorte que vous ne devriez pas obtenir de valeurs non entières en cas de cast ::integer. Il gère également les valeurs NULL (elles ne correspondront pas à la regexp).

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

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

Answer 2

Vous pouvez également créer votre propre fonction de conversion, à l'intérieur de laquelle vous peut 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;

Test :

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

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 distribution.

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

Les valeurs de type texte/varchar comme "b3ad5" donneront également des chiffres.

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

Pour répondre à la préoccupation de Chris Cogdon concernant le fait que la solution ne donne 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);

Elle fonctionne de la même manière que les solutions plus simples, sauf qu'elle donne 0 lorsque la valeur à convertir est constituée uniquement de caractères non numériques, comme "mauvais" :

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

Answer 4

C'est peut-être un peu une pirouette, mais cela a fait l'affaire 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 y 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 d'essai :

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 requête donnera le résultat suivant :

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

Attendu qu'il faut choisir uniquement values::integer entraînera un message d'erreur.

J'espère que cela vous aidera.

Answer 5

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

Je n'ai jamais travaillé avec PostgreSQL, mais j'ai vérifié l'adresse du site Web de l'entreprise. manuel pour la syntaxe correcte des instructions IF dans les requêtes SELECT.