ERROR: invalid input syntax for type numeric: “N/A” … nice but which column?

0
votes

I am running a function in PostgreSQL which contains several functions which purpose is to load data from foreign tables into mine. While running I get this error:

[SELECT - 0 row(s), 762.353 secs] [Error Code: 0, SQL State: 22P02] ERROR: invalid input syntax for type numeric: "N/A" Where: PL/pgSQL function import_data_3(integer,timestamp with time zone,timestamp with time zone,integer) line 16 at SQL statement SQL statement "SELECT import_data_3(import, beg, end_t, is_l)" PL/pgSQL function data_import_all(timestamp with time zone,timestamp with time zone,integer,integer) line 31 at PERFORM

I know what that means and also its reason I am trying to convert 'N/A' string to number and there for this error, problem is this lousy error does not specify the column where the error occurred.

Is there a way to get more precise error message which would tell me the exact column where the error happened?

1
postgresqlerror-handlingpostgresql-9.5
show us the query you are using, and some sample data to have idea what are you doing. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. - Juan Carlos Oropeza

1 Answers

0
votes

It's not in the column but in one of the parameters that are passed to import_data_3.

You called the query SELECT import_data_3(import, beg, end_t, is_l).

The specification of this function is import_data_3(integer,timestamp with time zone,timestamp with time zone,integer)

Since you got the error

invalid input syntax for type numeric: "N/A"

this most likely means that either first parameter "import" or the last one "is_l" contain the value "N/A". Try to debug the calling code of the function data_import_all and see how those two parameter values are generated.