Error "invalid input syntax for integer" for a non-integer?

Question

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?

Answer 1

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.