“数字类型的输入语法无效"进入“空" [英] "invalid input syntax for type numeric" for entering "emptyness"

问题描述

我有一个使用 NUMERIC 类型的包含三列的表.但是，其中两个什么都没有(即空).这是代码:

I have a table with three columns using the NUMERIC type. However, two of them have nothing (ie. empty). Here it's the code:

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 列未填充.我发现它是空的，因为我之后用以下内容更新了两列:

given that I'm updating my ts column with these two columns having nothing on them (NR_TRACES and TRACE_SPACE_M), the ts column is not populated. I found that it's empty because I updated afterwards both columns with:

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 列已完全填充，因此我可以说数字"类型不允许以我使用的方式进行合并.我也使用了下面的一个没有成功:

the column LENGTH_M, however, is completely populated, so I can say that "numeric" type doesn't allow coalesce in the way I'm using it. I also used the one below without success:

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

如果我单独测试填充的 LENGTH_M 列，我会得到相同的指向 ''(空)空间:

if I test the populated LENGTH_M column alone, I get the same pointing to the '' (empty) space:

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

如何在不首先填充这两个空列的情况下规避此问题?

How can I circumvent this without populating in the first place these two empty columns?

感谢您对此的任何提示，提前致谢.

I'd appreciate any hints on this, thanks in advance.

推荐答案

COALESCE 的工作原理是它假定第一个参数的数据类型，然后尝试将后续值强制转换为第一个数据类型.所以下面显示了同样的错误，因为 '' 不能被转换为数字:

How COALESCE works is that it assumes the datatype from the first argument, then attempts to cast subsequent values to the first datatype. So the following shows the same error, since '' cannot be cast as numeric:

SELECT COALESCE(NULL::numeric, '')

这是通过将第一个参数转换为 text 来解决的:

This is fixed by casting the first argument to text:

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

因此可以使用 coalesce(length_m::text,'') 修复您的代码.

therefore your code could be fixed using coalesce(length_m::text,'').

类似的方法是收集文本数组中的项目，然后 用 ' ' 和 '' 将数组连接到一个字符串，用于空元素.

A similar approach is to collect the items in a text array, then join the array to a string with ' ', and '' for null elements.

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

这篇关于“数字类型的输入语法无效"进入“空"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！