错误“整数的无效输入语法"对于非整数? [英] Error "invalid input syntax for integer" for a non-integer?

问题描述

我正在使用 Python 和 psycopg2.

我正在尝试使用这样的值列表执行准备好的语句:

cur.callproc(prepared_func,prepared_values)psycopg2.DataError:整数的无效输入语法:WwLEA6XZ0V"第 1 行:... fn_approve_external_lead(ARRAY[1004, 2, 1079412, 'WwLEA6XZ0...

Postgres 中的函数如下所示:

创建或替换函数 fn_approve_external_lead(internal_usrid bigint, ofr_src_id bigint, ofr_ext_id varchar, puuid varchar, 支付 varchar, p_note varchar DEFAULT '') ...

所以据我所知，我将一个非 int 值放入一个非 int 参数中，所以它应该可以工作，但出于某种原因，它认为这个值是一个 int?

什么会导致这种情况?

解决方案

您的输入失败并在 ARRAY 构造函数 甚至在您到达 函数类型解析 - 一旦您删除了根本不应该在调用中的 ARRAY 构造函数，您将由于其他类型不匹配而收到更多错误消息.

您的解决方案是传递匹配类型的单独参数，不是数组.剩下的只是对我们所见的解释.

到底发生了什么

数组只能包含相同类型的值，但您的输入混合了 数字常量 带有非数字 字符串文字，无法解析.

如果你想让它解析为 text[](text 的数组)，你必须像这样提供字符串文字:

SELECT ARRAY['1004', '2', '1079412', 'WwLEA6XZ0'];

或者直接一个数组字面量，像这样:

SELECT '{1004, 2, 1079412, WwLEA6XZ0}'::text[];

但是一旦您使用数字常量启动 ARRAY 构造函数，则只能添加数字类型.或者至少是内容可以被强制为相同类型的字符串文字.你可以:

SELECT ARRAY[1004, 2, 1079412, '1'];

... 导致 int[].或者:

SELECT ARRAY[1004, 2, 1079412, 1.0];

甚至:

SELECT ARRAY[1004, 2, 1079412.0, '1.0'];

...两者都产生 numeric[].

但是 ARRAY[1004, 2, 1079412, 'WwLEA6XZ0V'] 是非法的，理所当然.

可以在手册的这一章中找到数组构造函数类型解析的确切规则:"类型转换" - "UNION、CASE 和相关结构".您的旅程结束于:

<块引用>

7. 将所有输入转换为所选类型.如果没有从给定输入转换为所选类型，则失败.

由于字符串文字 'WwLEA6XZ0V' 无法转换为 integer.

I am using Python and psycopg2.

I am attempting to execute a prepared statement with a list of values as such:

cur.callproc(prepared_func, prepared_values)

psycopg2.DataError: invalid input syntax for integer: "WwLEA6XZ0V"
LINE 1: ... fn_approve_external_lead(ARRAY[1004, 2, 1079412, 'WwLEA6XZ0...

The function in Postgres looks as follows:

CREATE OR REPLACE FUNCTION fn_approve_external_lead(
     internal_usrid bigint
   , ofr_src_id bigint
   , ofr_ext_id varchar
   , puuid varchar
   , paid varchar
   , p_note varchar DEFAULT '') ...

So as far as I can tell I am putting a non-int value into a non-int argument, so it should work, but for some reason it thinks this value is an int?

What would cause this?

解决方案

Your input fails with a syntax error in the ARRAY constructor before you even get to function type resolution - where you will get more error messages due to other type mismatches once you remove the ARRAY constructor that shouldn't be in the call at all.

Your solution is to pass separate parameters of matching type, not an array. The rest is just explanation of what we saw.

What really happened

An array can only include values of the same type, but your input mixes numeric constants with a non-numeric string literal, which cannot be resolved.

If you want it to resolve to text[] (array of text), you have to provide string literals like so:

SELECT ARRAY['1004', '2', '1079412', 'WwLEA6XZ0'];

Or an array literal directly, like so:

SELECT '{1004, 2, 1079412, WwLEA6XZ0}'::text[];

But once you start an ARRAY constructor with numeric constants, only numeric types are legal to add. Or at least string literals with content that can be coerced to the same type. You could:

SELECT ARRAY[1004, 2, 1079412, '1'];

... resulting in int[]. Or:

SELECT ARRAY[1004, 2, 1079412, 1.0];

Or even:

SELECT ARRAY[1004, 2, 1079412.0, '1.0'];

... both resulting in numeric[].

But ARRAY[1004, 2, 1079412, 'WwLEA6XZ0V'] is illegal and rightfully so.

The exact rules of type resolution for array constructors can be found in this chapter of the manual: "Type Conversion" - "UNION, CASE, and Related Constructs". You journey ends at:

7. Convert all inputs to the selected type. Fail if there is not a conversion from a given input to the selected type.

Since the string literal 'WwLEA6XZ0V' cannot be converted to integer.

这篇关于错误“整数的无效输入语法"对于非整数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！