Ошибка неверный синтаксис для типа numeric - Исправление ошибок и поиск оптимальных решений проблем

SELECT Concat(npi.first_name, ' ', npi.middle_name, ' ', npi.last_name) AS othername, npi.nhif_persons_identity_serial,
  CASE WHEN npi.person_identity_number IS NULL THEN '' WHEN Trim(npi.person_identity_number) = '' THEN ''
    ELSE npi.contact_email END, npi.biometric_member_number, npi.birth_certificate_number, npi.national_id_number, CASE
    WHEN npi.national_passport_number IS NULL THEN '' WHEN Trim(npi.national_passport_number) = '' THEN ''
    ELSE npi.national_passport_number END, CASE WHEN npi.national_pin_number IS NULL THEN ''
    WHEN Trim(npi.national_pin_number) = '' THEN '' ELSE npi.national_pin_number END, CASE
    WHEN npi.contact_email IS NULL THEN '' WHEN Trim(npi.contact_email) = '' THEN '' ELSE npi.contact_email END, CASE
    WHEN npi.address_district IS NULL THEN '' WHEN Trim(npi.address_district) = '' THEN '' ELSE npi.address_district
  END, CASE WHEN npi.address_location IS NULL THEN '' WHEN Trim(npi.address_location) = '' THEN ''
    ELSE npi.address_location END, CASE WHEN npi.address_county IS NULL THEN ''
    WHEN Trim(npi.address_county) = '' THEN '' ELSE npi.address_county END, CASE WHEN npi.work_code IS NULL THEN ''
    WHEN Trim(npi.work_code) = '' THEN '' ELSE npi.work_code END, CASE WHEN npi.address_physical IS NULL THEN ''
    WHEN Trim(npi.address_physical) = '' THEN '' ELSE npi.address_physical
  END
  , npi.date_of_birth  , npi.date_registered, npi.date_added,CASE WHEN npi.gender IS NULL THEN ''
    WHEN Trim(npi.gender) = '' THEN '' ELSE npi.gender
  END
  , npi.first_name, npi.middle_name, npi.last_name,

   CASE WHEN npi.marital_status IS NULL THEN ''
    WHEN Trim(npi.marital_status) = '' THEN '' ELSE npi.marital_status
  END
  ,CASE WHEN npi.contact_address IS NULL THEN ''
    WHEN Trim(npi.contact_address) = '' THEN '' ELSE npi.contact_address
  END
  ,CASE WHEN npi.contact_telephone IS NULL THEN ''
    WHEN Trim(npi.contact_telephone) = '' THEN '' ELSE npi.contact_telephone
  END
  ,CASE WHEN npi.contact_mobile IS NULL THEN ''
    WHEN Trim(npi.contact_mobile) = '' THEN '' ELSE npi.contact_mobile
  END
  ,CASE WHEN npi.mobile_contact_detail IS NULL THEN ''
    WHEN Trim(npi.mobile_contact_detail) = '' THEN '' ELSE npi.mobile_contact_detail
  END
  ,CASE WHEN npi.temp_member_number IS NULL THEN ''
     ELSE npi.temp_member_number
  END
  ,CASE WHEN npi.national_nhif_number IS NULL THEN ''
    WHEN Trim(npi.national_nhif_number) = '' THEN '' ELSE npi.national_nhif_number
  END
  ,CASE WHEN nbci.facial_full_image IS NULL THEN ''
    WHEN Trim(nbci.facial_full_image) = '' THEN '' ELSE nbci.facial_full_image
  END
  ,CASE WHEN nbci.facial_scaled_image IS NULL THEN ''
    WHEN Trim(nbci.facial_scaled_image) = '' THEN '' ELSE nbci.facial_scaled_image
  END

FROM nhif_persons_identities npi
  INNER JOIN nhif_biometric_cards nbc ON npi.nhif_persons_identity_serial = nbc.nhif_persons_identity_serial
  INNER JOIN nhif_biometric_card_images nbci ON nbci.nhif_biometric_card_serial = nbc.nhif_biometric_card_serial
WHERE npi.nhif_persons_identity_serial = 293182

Am selecting data from database and if the column doesn’t contain any value it output «blank» , Though am running into the error below when i execute the query i have tried to search but the answers i have got doesn’t work.
It seems its the error with numeric.

ERROR:  invalid input syntax for type numeric: ""
LINE 35:   ,CASE WHEN npi.temp_member_number IS NULL THEN ''
                                                          ^

********** Error **********

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

Источник

SELECT Concat(npi.first_name, ' ', npi.middle_name, ' ', npi.last_name) AS othername, npi.nhif_persons_identity_serial,
  CASE WHEN npi.person_identity_number IS NULL THEN '' WHEN Trim(npi.person_identity_number) = '' THEN ''
    ELSE npi.contact_email END, npi.biometric_member_number, npi.birth_certificate_number, npi.national_id_number, CASE
    WHEN npi.national_passport_number IS NULL THEN '' WHEN Trim(npi.national_passport_number) = '' THEN ''
    ELSE npi.national_passport_number END, CASE WHEN npi.national_pin_number IS NULL THEN ''
    WHEN Trim(npi.national_pin_number) = '' THEN '' ELSE npi.national_pin_number END, CASE
    WHEN npi.contact_email IS NULL THEN '' WHEN Trim(npi.contact_email) = '' THEN '' ELSE npi.contact_email END, CASE
    WHEN npi.address_district IS NULL THEN '' WHEN Trim(npi.address_district) = '' THEN '' ELSE npi.address_district
  END, CASE WHEN npi.address_location IS NULL THEN '' WHEN Trim(npi.address_location) = '' THEN ''
    ELSE npi.address_location END, CASE WHEN npi.address_county IS NULL THEN ''
    WHEN Trim(npi.address_county) = '' THEN '' ELSE npi.address_county END, CASE WHEN npi.work_code IS NULL THEN ''
    WHEN Trim(npi.work_code) = '' THEN '' ELSE npi.work_code END, CASE WHEN npi.address_physical IS NULL THEN ''
    WHEN Trim(npi.address_physical) = '' THEN '' ELSE npi.address_physical
  END
  , npi.date_of_birth  , npi.date_registered, npi.date_added,CASE WHEN npi.gender IS NULL THEN ''
    WHEN Trim(npi.gender) = '' THEN '' ELSE npi.gender
  END
  , npi.first_name, npi.middle_name, npi.last_name,

   CASE WHEN npi.marital_status IS NULL THEN ''
    WHEN Trim(npi.marital_status) = '' THEN '' ELSE npi.marital_status
  END
  ,CASE WHEN npi.contact_address IS NULL THEN ''
    WHEN Trim(npi.contact_address) = '' THEN '' ELSE npi.contact_address
  END
  ,CASE WHEN npi.contact_telephone IS NULL THEN ''
    WHEN Trim(npi.contact_telephone) = '' THEN '' ELSE npi.contact_telephone
  END
  ,CASE WHEN npi.contact_mobile IS NULL THEN ''
    WHEN Trim(npi.contact_mobile) = '' THEN '' ELSE npi.contact_mobile
  END
  ,CASE WHEN npi.mobile_contact_detail IS NULL THEN ''
    WHEN Trim(npi.mobile_contact_detail) = '' THEN '' ELSE npi.mobile_contact_detail
  END
  ,CASE WHEN npi.temp_member_number IS NULL THEN ''
     ELSE npi.temp_member_number
  END
  ,CASE WHEN npi.national_nhif_number IS NULL THEN ''
    WHEN Trim(npi.national_nhif_number) = '' THEN '' ELSE npi.national_nhif_number
  END
  ,CASE WHEN nbci.facial_full_image IS NULL THEN ''
    WHEN Trim(nbci.facial_full_image) = '' THEN '' ELSE nbci.facial_full_image
  END
  ,CASE WHEN nbci.facial_scaled_image IS NULL THEN ''
    WHEN Trim(nbci.facial_scaled_image) = '' THEN '' ELSE nbci.facial_scaled_image
  END

FROM nhif_persons_identities npi
  INNER JOIN nhif_biometric_cards nbc ON npi.nhif_persons_identity_serial = nbc.nhif_persons_identity_serial
  INNER JOIN nhif_biometric_card_images nbci ON nbci.nhif_biometric_card_serial = nbc.nhif_biometric_card_serial
WHERE npi.nhif_persons_identity_serial = 293182

ERROR:  invalid input syntax for type numeric: ""
LINE 35:   ,CASE WHEN npi.temp_member_number IS NULL THEN ''
                                                          ^

********** Error **********

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

Источник

Я пытаюсь отладить функцию, созданную не мной (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;

2 ответа

Лучший ответ

Я понял, в чем проблема. Похоже, что 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

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

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

Dolf Andringa
25 Июн 2017 в 03:38

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

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 для решения вашей проблемы.

Michel Milezzi
24 Июн 2017 в 13:49

Источник

Что сказал @Laurenz.

Но почему «странное» сообщение об ошибке?

Ваша синтаксическая ошибка (0,8 вместо 0.8 в cour_g=(((upox_sum+epil_sum)/2)*0,8);) запутано добавленными, безвозмездными скобками, которые интерпретируются как конструкторы ROW — ключевое слово ROW — дополнительный шум.

Что вы видите в сообщении об ошибке:

ERROR:  invalid input syntax for type numeric: "(0.0000000000000000,8)"

… представляет собой выражение строки, состоящее из полей: 0.0000000000000000 и 8.
Без вводящих в заблуждение круглых скобок назначение:

cour_g=((upox_sum+epil_sum)/2)*0,8;  -- still incorrect: 0,8

… произвело бы другое (более открытое) сообщение об ошибке:

ERROR:  query "SELECT ((upox_sum+epil_sum)/2)*0,8" returned 2 columns

потому что запятая (,) интерпретируется как разделитель столбцов.

Это сообщение также показывает, что PL/pgSQL оценивает каждое голое выражение с помощью (основного и быстрого) SELECT внутренне. Язык в основном представляет собой оболочку вокруг ядра SQL. Это объясняет, почему присвоения немного дороже, чем можно ожидать от других PL.

Ваш исходный оператор эквивалентен более подробному синтаксису ROW:

cour_g=ROW(((upox_sum + epil_sum)/2)*0,8);  -- still incorrect: 0,8

… который функционально эквивалентен:

cour_g=(SELECT ROW(((upox_sum + epil_sum)/2)*0,8));  -- still incorrect: 0,8

Оба результата приводят к тому же сообщению об ошибке, которое вы наблюдали.

Или:

cour_g=(SELECT ((upox_sum + epil_sum)/2)*0,8);  -- still incorrect: 0,8

С другим (уже более показательным) сообщением об ошибке:

ERROR:  subquery must return only one column

Требуются скобки вокруг вложенного явного SELECT. Если вы отбросите ненужный SELECT, оставьте с ним скобки-скобки или они интерпретируются как конструктор ROW (с или без шумового слова ROW).

Поэтому используйте:

cour_g := (upox_sum + epil_sum) / 2 * 0.8;

((upox_sum+epil_sum)/2) вокруг ((upox_sum+epil_sum)/2) также излишни, потому что * и / имеют один и тот же приоритет оператора и в любом случае оцениваются слева направо. Но в этом случае добавленные круглые скобки представляют собой безопасный шум (что может или не поможет читаемости).

Или упростить:

cour_g := (upox_sum + epil_sum) * 0.4;

Тем не менее, поскольку присвоения сравнительно дороги в PL/pgSQL, попробуйте применить стиль программирования с меньшим количеством назначений.
И не все безвозмездные круглые скобки безвредны.

Источник