输入"emptyness"时，出现了"invalid input syntax for type numeric"错误。

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

考虑到我正在使用这两个没有内容的列（NR_TRACES和TRACE_SPACE_M）更新我的ts列，但是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已经完全填充，所以我可以说"numeric"类型不允许像我这样使用coalesce。我也尝试了下面这个但是没有成功：

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,'') || ' '...
                                                         ^

我该如何绕过这个问题，而不必事先填充这两个空列呢？如果您有任何提示，我将不胜感激。谢谢！

- Gery

---

看起来你正在更新一个 numeric 列，而不是一个 tsvector 列。此外，在 SQL 中引用大写的 TS 名称，需要使用双引号，例如 "TS"。 - Mike T

@MikeToews 感谢 Mike 的回复。实际上，ts 列是通过一条 update 语句进行填充的。有关详细信息，请参阅我的问题，我最近更新了它。 - Gery

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

- Mike T

---

谢谢Mike，虽然这个方法可行，但如果我使用UPDATE profiles SET nr_traces=10131, trace_space_m=12.5 WHERE PROFILE = '93-30';更新这些数字列，仍会出现“tsvector列"TS"不存在”的错误，那么我就不能再用数字更新这些列了吗？ - Gery

只是想指出，我有一个用于插入或更新的触发器，如下所示：CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON profiles FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('TS', 'pg_catalog.english', ID, FID, SURVEY, PROFILE, TYPE, SOURCE, NR_TRACES, TRACE_SPACE_M, LENGTH_M, IMAGES, COMMENTS);，我认为这里存在问题。 - Gery

列名需要是'ts'，因为Postgres区分大小写。或者，您可以将列名重命名为"TS"，但长期来看，总是要双引号引用事物会很烦人。 - Mike T

我已将代码更改为“ts”，但问题仍然存在。然后我再次运行脚本，但不使用触发器，此后更新成功了，因此触发器是问题所在。有没有办法修改触发器或其中的某些内容？ - Gery

我最近在触发器中尝试使用NR_TRACES::numeric，但出现了“::”附近的语法错误。 - Gery