Sql error 22p02 error invalid input syntax for type numeric

spicehead-2ji2d wrote:

Hello, 

I’m getting the following error — 

SQL Error [22P02]: ERROR: invalid input syntax for type numeric:

SQL appears not to like the «.» in the where clause, but pmd001.prod_no_x is defined as bpchar with the data of  «FLY       0.5  MMB16»  in the table.

Snippet —>

full join public.cond_ccode on cond_ccode.conduct_code = condsingle.conduct_code
where pmd001.prod_no_x = ‘FLY     0.5 MMB16’ and substring(rtd003.mach_key_x,5,2)

What can I do to retrieve this kind of data out of the table?

Thanks in advance.

Is this the end of the WHERE condition statement?

… where pmd001.prod_no_x = ‘FLY     0.5 MMB16’ and substring(rtd003.mach_key_x,5,2)

If yes, you are missing something at the end where you need to compare ‘

substring(rtd003.mach_key_x,5,2) to something else.
If you leave it as is, SQL will try to interpret as a logical TRUE/FALSE value (1 or 0).  Obviously a substring function returns a string so that will likely not return 1 or 0 unless the returned string itself is ‘1’ or ‘0’ and the implicit conversion then returns 1 or 0.  However if the string is anything else, the condition will fail.

There is a little more to the where clause, I didn’t want to confuse the issue, the problem is in this part of the where clause —> »

pmd001.prod_no_x = ‘FLY     0.5 MMB16 ‘».   

Never mind.   Found issue in join command, which on this record produced my error.   All good now.

1 ответы

Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками

c#
postgresql

or задайте свой вопрос.

Я пытаюсь отладить функцию, созданную не мной (dms2dd). Я сделал свою собственную тестовую функцию (см. Ниже) и свел мою проблему к определенной строке / значению.

Если я запускаю следующий запрос:

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

Я получаю следующий вывод:

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

Это именно то, что я ожидаю. Но со следующим запросом:

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

Я получаю ошибку

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

Ошибка предполагает, что значение varchar, которое я пытался привести к числовому, пусто, но, как вы можете видеть из предыдущего запроса, это не так. Это просто действительный числовой varchar. На самом деле, если я копирую-вставляю значение и запускаю:

SELECT CAST('9.4824' AS numeric);

Это полностью работает, и запрос фактически приводит к правильному числовому значению. Более того, если я сохраню результаты первого запроса в промежуточной таблице с помощью:

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

А затем выдать

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

Это полностью работает. Даже это работает просто отлично:

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

Так что здесь не так? Похоже, что во втором запросе, где я приводил числовые значения, моей функции передается другое значение, но я протестировал столбец сортировки (индекс) и он содержит только уникальные bigints.

Это код для функции test_dolf:

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;

Я понял, в чем проблема. Похоже, что postgresql, хотя я делаю LIMIT и OFFSET, все еще вызывает функции в select для других строк за пределами этого фрейма.

Я понял это, поместив код, который вызвал исключение, в мою функцию и перехватив полученную ошибку, и вызвал ошибку NOTICE, когда это исключение произошло (см. Функцию ниже, в частности, блок BEGIN EXCEPTION END в конце функции). Уведомление отображается как предупреждение, но не останавливает выполнение кода. Внезапно оказалось, что функция вызывается не только для строки, к которой я ожидал, но также и для целого ряда других строк. Это совсем не то, что я ожидал, и для меня это немного противоречит интуиции, но я предполагаю, что именно так должен работать postgresql.

Поскольку перехват исключений в postgresql довольно дорог, я думаю, мне нужно добавить тест, который в первую очередь предотвращает исключение (я мог бы проверить длину arrDegMinSec и значение элементов 1-3 этого массива и вернуть NULL в случае недопустимых значений.

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;

< Сильный > ИЗМЕНИТЬ

Предоставлено @ michel.milezzi. Другим решением, не требующим изменения функции, является изменение вызова функции в запросе на

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

И действительно, как подсказывает @abelisto, я мог бы также поместить запрос в подзапрос и преобразовать его в числовой в основном запросе так:

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

Это действительно предотвратило бы проблему, которая действительно упростила бы процесс отладки.

При этом я все равно собирался изменить функцию (чтобы сделать ее более надежной для грязных данных), поэтому для меня это было лучшим решением.

Ошибка, которую вы получаете, заключается в следующем:

ERROR: invalid input syntax for type numeric: ""

Итак, он пытается привести пустую строку к числовому значению. Как насчет использования NULLIF функции для решения этой проблемы?

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;

Также вы можете захотеть увидеть план выполнения, чтобы понять эту проблему. Может случиться так, что LIMIT и OFFSET выполняются сразу после приведения. Это объясняет, почему вы не видите строку с пустой строкой.

РЕДАКТИРОВАТЬ

Ой, я должен прочитать ваш ответ, прежде чем отправлять это. В любом случае вы все равно можете использовать NULLIF для решения вашей проблемы.