Как в Postgres любую строку привести к 0 или '0'?

Question

[McMike]

В списке числовых значений иногда попадаются строки, нужно их интерпретировать как 0.
Как это сделать на уровне Postgres'а? Функции типа CAST переводят из строки в число только строки соответствующие числам, а например SELECT cast('asd' as INT) выдаст ошибку.

Answer 1

[Сергей Горностаев]

Это костыль, но работать будет

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

Если прав в БД достаточно, то можно определить своё правило приведения типов:

CREATE OR REPLACE FUNCTION cast_to_int(text) RETURNS integer AS $$
BEGIN
    RETURN CAST($1::varchar AS integer);
EXCEPTION
    WHEN invalid_text_representation THEN
        RETURN 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE CAST (text as integer) WITH FUNCTION cast_to_int(text);

SELECT CAST('asd'::text as integer) вернёт 0

Answer 2

[Medin K]

можно и так
select regexp_replace('111a', '^(.*)[^\d].*$', '0')
-- 111 = 111
-- a111 = 0
-- 1a11 = 0
-- 111a = 0
-- sdfыва11ыва1a = 0