postgresql-9.2 - "invalid input syntax for type numeric"用于输入 "emptyness"

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,'') 修复您的代码.

类似的方法是收集文本数组中的项目，然后 join the array to a string与 ' ' , 和 ''对于空元素。

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],
    ' ', '')
  )