Postgresql weird invalid input syntax for type numeric: "", and the value is not empty varchar

Question

I am trying to debug a function not created by me ( dms2dd ). I made my own test function (see below) and cast my problem back to a specific line / value.

If I run the following query:

SELECT "Lat", "Long", test_dolf("Lat"), test_dolf("Long") FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index LIMIT 1 OFFSET 29130 

I get the following output:

 'N6° 6' 9.4824"';'E118° 26' 49.1172'' ';'9.4824';'49.1172' 

what i expect. But with the following query:

 SELECT "Lat", "Long", CAST(test_dolf("Lat") as numeric), test_dolf("Long") FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index LIMIT 1 OFFSET 29130 

I get an error

 ERROR: invalid input syntax for type numeric: "" SQL state: 22P02 

The error indicates that the varchar value that I am trying to use for numeric is empty, but as you can see from the previous query, it is not. This is just a valid numeric varchar. In fact, if I copy-paste the value and run:

 SELECT CAST('9.4824' AS numeric); 

This fully works, and in fact the query results in a valid numeric. Moreover, if I save the results of the first query in a staging table with:

 SELECT "Lat", "Long", test_dolf("Lat") as lat_sec, test_dolf("Long") as long_sec INTO dms2dd_test FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index LIMIT 11 OFFSET 29120 

and then enter

 SELECT CAST(long_sec as numeric), CAST(lat_sec AS numeric) FROM dms2dd_test; 

It works completely. Even this works great:

 SELECT test_dolf(E'N6° 6\' 9.4824"')::numeric as lat_sec 

So what is going on here? It seems that in the second query, in which I passed a numerical value, another value is passed to my function, but I tested the sort column (index) and contains only unique bandages.

This is the code for the test_dolf function:

 CREATE OR REPLACE FUNCTION public.test_dolf(strdegminsec character varying) RETURNS varchar AS $BODY$ DECLARE i numeric; intDmsLen numeric; -- Length of original string strCompassPoint Char(1); strNorm varchar(16) = ''; -- Will contain normalized string strDegMinSecB varchar(100); blnGotSeparator integer; -- Keeps track of separator sequences arrDegMinSec varchar[]; -- TYPE stringarray is table of varchar(2048) ; strChr Char(1); BEGIN strDegMinSec := regexp_replace(replace(strdegminsec,E'\'\'','"'),' "([0-9]+)',E' \\1"'); -- Remove leading and trailing spaces strDegMinSecB := REPLACE(strDegMinSec,' ',''); intDmsLen := Length(strDegMinSecB); blnGotSeparator := 0; -- Not in separator sequence right now -- Loop over string, replacing anything that is not a digit or a -- decimal separator with -- a single blank FOR i in 1..intDmsLen LOOP -- Get current character strChr := SubStr(strDegMinSecB, i, 1); -- either add character to normalized string or replace -- separator sequence with single blank If strpos('0123456789,.', strChr) > 0 Then -- add character but replace comma with point If (strChr <> ',') Then strNorm := strNorm || strChr; Else strNorm := strNorm || '.'; End If; blnGotSeparator := 0; ElsIf strpos('neswNESW',strChr) > 0 Then -- Extract Compass Point if present strCompassPoint := strChr; Else -- ensure only one separator is replaced with a blank - -- suppress the rest If blnGotSeparator = 0 Then strNorm := strNorm || ' '; blnGotSeparator := 0; End If; End If; End Loop; -- Split normalized string into array of max 3 components arrDegMinSec := string_to_array(strNorm, ' '); return arrDegMinSec[3]; End $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100; 

Answer 1

I realized what the problem is. It looks like postgresql, although I am performing LIMIT and OFFSET, it still calls the functions in the selection for other lines outside this frame.

I realized this by putting the code that threw an exception inside my function and catching the resulting error, and raising a NOTICE error when this exception occurred (see the function below, in particular, the BEGIN EXCEPTION END block at the end of the function). The notification appears as a warning, but does not stop the code from executing. It suddenly became clear that the function was not just called for a string that I expected from it, but for a number of other lines. This is not at all what I expected, and for me it seems like the counter is intuitive, but I think postgresql should work this way.

Since catching exceptions is quite expensive in postgresql, I think I need to add a test that prevents the exception in the first place (I could check the length of arrDegMinSec and the value of elements 1-3 of this array and return NULL in case of invalid values.

 CREATE OR REPLACE FUNCTION public.test_dolf(strdegminsec character varying) RETURNS numeric AS $BODY$ DECLARE i numeric; intDmsLen numeric; -- Length of original string strCompassPoint Char(1); strNorm varchar(16) = ''; -- Will contain normalized string strDegMinSecB varchar(100); blnGotSeparator integer; -- Keeps track of separator sequences arrDegMinSec varchar[]; -- TYPE stringarray is table of varchar(2048) ; strChr Char(1); retval numeric; BEGIN strDegMinSec := regexp_replace(replace(strdegminsec,E'\'\'','"'),' "([0-9]+)',E' \\1"'); -- Remove leading and trailing spaces strDegMinSecB := REPLACE(strDegMinSec,' ',''); intDmsLen := Length(strDegMinSecB); blnGotSeparator := 0; -- Not in separator sequence right now -- Loop over string, replacing anything that is not a digit or a -- decimal separator with -- a single blank FOR i in 1..intDmsLen LOOP -- Get current character strChr := SubStr(strDegMinSecB, i, 1); -- either add character to normalized string or replace -- separator sequence with single blank If strpos('0123456789,.', strChr) > 0 Then -- add character but replace comma with point If (strChr <> ',') Then strNorm := strNorm || strChr; Else strNorm := strNorm || '.'; End If; blnGotSeparator := 0; ElsIf strpos('neswNESW',strChr) > 0 Then -- Extract Compass Point if present strCompassPoint := strChr; Else -- ensure only one separator is replaced with a blank - -- suppress the rest If blnGotSeparator = 0 Then strNorm := strNorm || ' '; blnGotSeparator := 0; End If; End If; End Loop; -- Split normalized string into array of max 3 components arrDegMinSec := string_to_array(strNorm, ' '); BEGIN retval := arrDegMinSec[3]::numeric; return retval; EXCEPTION WHEN SQLSTATE '22P02' THEN RAISE NOTICE 'Incorrect value %', strDegMinSec; RETURN NULL; END; End $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100; 

---

EDIT

Provided by @ michel.milezzi, another solution that does not require modifying the function is to change the function call in the request to

 CAST(NULLIF(test_dolf("Lat"), '') as numeric) 

And indeed, as @abelisto suggests, I could also put the request in a subquery and only attribute it to a number in the main request like this:

 SELECT "Lat", "Long", CAST(test_dolf("Lat") as numeric), test_dolf("Long") FROM (SELECT * FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index LIMIT 1 OFFSET 29130) as t 

This would really prevent a problem that would really simplify the debugging process.

As I said, I was going to change the function anyway (to make it more reliable for dirty data), so for me it was a better solution.

Answer 2

Error receiving:

 ERROR: invalid input syntax for type numeric: "" 

So, he is trying to pass an empty string to a numeric value. How about using NULLIF to solve this problem?

 SELECT "Lat", "Long", CAST(NULLIF(test_dolf("Lat"), '') as numeric), test_dolf("Long") FROM pawikan WHERE "Lat" IS NOT NULL AND "Long" IS NOT NULL ORDER BY index LIMIT 1 OFFSET 29130; 

You can also see the implementation plan to understand this problem. What can happen is that LIMIT and OFFSET executed immediately after casting. This explains why you do not see a line with an empty line.

<h / "> EDIT

Unfortunately, I have to read your answer before posting this. In any case, you can still use NULLIF to get around the problem.