postgresql postgres 检查是否为空然后转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9218351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres check if null then cast to numeric
提问by user373201
I am trying check if a value is null if so the select null else cast to numeric, but it throws an error. This is actually part of an insert statement
我正在尝试检查一个值是否为空，如果是，则 select null else 转换为数字，但它会引发错误。这实际上是插入语句的一部分
INSERT into someTable(name,created,power)
SELECT 'xyz',now(),
case when :power ='null' then NULL else cast(:power as numeric) end from abc
error that I get is
我得到的错误是
Error: ERROR: invalid input syntax for type numeric: "null"
:power is a variable that can be given any value using java code. If I give a value of null it give an error.
:power 是一个变量，可以使用 java 代码赋予任何值。如果我给出一个 null 值，它会给出一个错误。
In code I get the following error from the java stack trace
在代码中，我从 java 堆栈跟踪中得到以下错误
org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to numeric
回答by Erwin Brandstetter
Error:
错误：
SELECT CASE WHEN 'null' = 'null' THEN NULL ELSE cast('null' AS numeric) END
No error:
没有错误：
DO $$
DECLARE
power text := 'null';
BEGIN
PERFORM CASE WHEN power = 'null' THEN NULL ELSE cast(power AS numeric) END;
END;
$$
Explanation:
解释：
If you build a query string, the expression cast('null' AS numeric)or simply 'null'::numericalwaysraises an exception, even in an ELSEblock that is never executed, because it is invalid input syntaxand the exception is raised during the syntax check (like the error message implies), not during execution.
如果您构建查询字符串，则表达式cast('null' AS numeric)or'null'::numeric总是会引发异常，即使在ELSE从未执行的块中也是如此，因为它是invalid input syntax并且异常是在语法检查期间引发的（如错误消息暗示的那样），而不是在执行期间。
A CASEstatement like you display only makes sense with a parameter or variable not with literals. The second instance of the literal has no connection to the first instance whatsoever after the query string has been assembled.
CASE像您显示的语句只对参数或变量有意义，而不对文字有意义。在组合查询字符串之后，文字的第二个实例与第一个实例没有任何联系。
For dynamic SQL like that, you need to check the value beforeyou build the query string. Or you use a function or prepared statement and pass the value as parameter. That would work, too.
对于这样的动态 SQL，您需要在构建查询字符串之前检查该值。或者您使用函数或准备好的语句并将值作为参数传递。那也行。
More advice after comment:
评论后的更多建议：
In your particular case you could check the value in the app and build a query string like this:
在您的特定情况下，您可以检查应用程序中的值并构建如下查询字符串：
INSERT INTO tbl(name, abc_id, created, power)
SELECT 'xyz'
, abc_id
, now()
, <insert_value_of_power_or_NULL_here> -- automatically converted to numeric
FROM abc
You may be interested in a different approach to INSERT data from a fileconditionally.
Use COPYfor files local to the serveror psql's meta-command \copyfor files local to the client.
您可能对有条件地从文件中插入数据的不同方法感兴趣。
使用COPY的文件服务器的本地或psql的元命令\复制的文件本地客户端。
回答by ramigg
回答by aleroot
You have to check with the IS operator, and not with the equal when you dealing with NULL :
在处理 NULL 时，您必须使用 IS 运算符进行检查，而不是使用 equal ：
INSERT into someTable(name,created,power)
SELECT 'xyz',now(),
case when :power IS null then NULL else cast(:power as numeric) end from abc
回答by maniek
INSERT into someTable(name,created,power) SELECT 'xyz',now(),
case :power when 'null' then NULL else :power end::numeric from abc
回答by MrCris
I was trying to do something similar in order to update/insert some records where a numeric value can be null or not. You can validate a variable before you send it to the function or inside the function depending the value passed (For me using a variable is better than use CASE WHEN THEN ELSE END CASE every time you need to validate the value)
我试图做类似的事情，以便更新/插入一些数值可以为空或不为空的记录。您可以在将变量发送到函数之前或在函数内部根据传递的值验证变量（对我来说，使用变量比每次需要验证值时使用 CASE WHEN THEN ELSE END CASE 更好）
So to work with the NULL values using a regular comparison operand in order to find a record to update can be done by turning transform_null_equals to ON
因此，要使用常规比较操作数处理 NULL 值以找到要更新的记录，可以通过将 transform_null_equals 设置为 ON 来完成
I hope this help someone
我希望这有助于某人
CREATE OR REPLACE FUNCTION update_insert_transaction(vcodaccount integer, vcodaccountaux text,
vdescription text, vcodgroup integer)
RETURNS integer AS $$
DECLARE
n integer = 0;
vsql text = 'NULL';
BEGIN
IF vcodaccountaux <> '' THEN
vsql = vcodaccountaux;
END IF;
SET LOCAL transform_null_equals TO ON;
EXECUTE 'UPDATE account_import_conf SET (codaccount, codaccountaux, description, codgroup) =
('||vcodaccount||','||vsql||',trim('||quote_literal(vdescription)||'),'||vcodgroup||')
WHERE codaccount='||vcodaccount||' AND codaccountaux = '||vsql||' RETURNING * ';
GET DIAGNOSTICS n = ROW_COUNT;
IF n = 0 THEN
EXECUTE 'INSERT INTO account_import_conf (codaccount, codaccountaux, description, codgroup)
SELECT '||vcodaccount||','||vsql||' ,trim('||quote_literal(vdescription)||'),'||vcodgroup||';';
END IF;
RETURN n;
END;$$
LANGUAGE plpgsql;
