Postgres：为CAST失败定义默认值吗？ [英] Postgres: define a default value for CAST failures?

问题描述

是否可以定义在 CAST 操作失败的情况下返回的默认值？

Is it possible to define a default value that will be returned in case a CAST operation fails?

例如，这样：

SELECT CAST('foo' AS INTEGER)

是否将返回默认值而不是抛出错误？

Will return a default value instead of throwing an error?

推荐答案

CAST 没有默认值：

There is no default value for a CAST:

类型转换指定从一种数据类型到另一种数据类型的转换。 PostgreSQL接受两种等效的类型转换语法：

A type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent syntaxes for type casts:

CAST ( expression AS type )
expression::type

除了强制转换的表达式外，语法中没有余地以及所需的目标类型。

There is no room in the syntax for anything other than the expression to be casted and the desired target type.

但是，您可以通过简单的功能手动完成此操作：

However, you can do it by hand with a simple function:

create or replace function cast_to_int(text, integer) returns integer as $$
begin
    return cast($1 as integer);
exception
    when invalid_text_representation then
        return $2;
end;
$$ language plpgsql immutable;

然后您可以说 cast_to_int（'pancakes'，0）并获得 0 。

PostgreSQL还允许您创建自己的演员表，因此您可以执行以下操作：

PostgreSQL also lets you create your own casts so you could do things like this:

create or replace function cast_to_int(text) returns integer as $$
begin
    -- Note the double casting to avoid infinite recursion.
    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('pancakes'::text as integer)

并获取 0 或您可以说

select cast(some_text_column as integer) from t

并得到 0 c $ c> some_text_column 值不是有效的整数。如果您想使用此自动默认转换强制转换 varchar s，则必须进行两次强制转换：

and get 0 for the some_text_column values that aren't valid integers. If you wanted to cast varchars using this auto-defaulting cast then you'd have to double cast:

select cast(some_varchar::text as integer) from t

选择强制转换（some_varchar :: text为整数）

仅仅因为您可以做到这一点，就不是一个好主意。我认为将标准文本替换为整数强制转换不是最好的主意。上述方法还要求您将标准 varchar 留给 integer 强制转换，如果需要，可以解决

Just because you can do this doesn't make it a good idea. I don't think replacing the standard text to integer cast is the best idea ever. The above approach also requires you to leave the standard varchar to integer cast alone, you could get around that if you wanted to do the whole conversion yourself rather than lazily punting to the built in casting.

将NULL处理留给读者（一种轻松的练习）。

NULL handling is left as an (easy) exercise for the reader.

这篇关于Postgres：为CAST失败定义默认值吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！