"неверный синтаксис ввода для типа числовой" для ввода "пустота"

Question

У меня есть таблица с тремя столбцами, используя тип NUMERIC. Однако два из них не имеют ничего (т. Е. Пусто). Вот код:

CREATE TABLE profiles(
ID SMALLINT,
FID SMALLINT,
SURVEY VARCHAR(100),
PROFILE VARCHAR(100),
TYPE VARCHAR(100),
SOURCE VARCHAR(100),
NR_TRACES NUMERIC,
TRACE_SPACE_M NUMERIC,
LENGTH_M NUMERIC,
IMAGES TEXT,
COMMENTS TEXT
);

ALTER TABLE profiles ADD ts tsvector;

UPDATE profiles SET ts = to_tsvector('english', ID || ' ' || FID || ' ' || coalesce(SURVEY,'') || ' ' || coalesce(PROFILE,'') || ' ' || coalesce(TYPE,'') || ' ' || coalesce(SOURCE,'') || ' ' || coalesce(NR_TRACES,'') || ' ' || coalesce(TRACE_SPACE_M,'') || ' ' || coalesce(LENGTH_M,'') || ' ' || coalesce(IMAGES,'') || ' ' || coalesce(COMMENTS,''));

учитывая, что я обновляю свой столбец ts с этими двумя столбцами, на которых ничего нет (NR_TRACES и TRACE_SPACE_M), столбец ts не заполняется. Я обнаружил, что он пуст, потому что впоследствии я обновил оба столбца:

UPDATE profiles SET nr_traces = 10131, trace_space_m = 12.5 WHERE PROFILE = '30';

и получил:

ERROR:  invalid input syntax for type numeric: ""

а также

ERROR:  tsvector column "TS" does not exist

однако столбец LENGTH_M полностью заполнен, поэтому я могу сказать, что "числовой" тип не позволяет объединяться так, как я его использую. Я также использовал приведенный ниже, но безуспешно:

coalesce(my-numeric-empty-column,'')
coalesce(my-numeric-empty-column,'')::numeric

если я проверяю заполненный столбец LENGTH_M в одиночку, я получаю то же самое, указывая на '' (пустое) пространство:

psql:profiles.sql:146: ERROR:  invalid input syntax for type numeric: ""
LINE 1: ... ' ' || TRACE_SPACE_M || ' ' || coalesce(LENGTH_M,'') || ' '...
                                                         ^

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

Буду признателен за любые намеки на это, заранее спасибо.

Answer 1

Как COALESCE работает то, что он принимает тип данных из первого аргумента, а затем пытается привести последующие значения к первому типу данных. Таким образом, следующее показывает ту же ошибку, так как '' не может быть приведен как числовой:

SELECT COALESCE(NULL::numeric, '')

Это исправлено приведением первого аргумента к text:

SELECT COALESCE(NULL::numeric::text, '')

поэтому ваш код может быть исправлен с помощью coalesce(length_m::text,''),

---

Аналогичный подход заключается в сборе элементов в текстовом массиве, а затем присоединении массива к строке с ' ', а также '' для нулевых элементов.

UPDATE profiles
SET
  ts = to_tsvector(
    'english',
    array_to_string(
      ARRAY[id::text, fid::text, survey, profile, type, source,
            nr_traces::text, trace_space_m::text, length_m::text,
            images, comments],
    ' ', '')
  )