"invalid input syntax for type numeric" for entering "emptyness"

Question

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

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

and got:

ERROR:  invalid input syntax for type numeric: ""

and

ERROR:  tsvector column "TS" does not exist

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

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.

Answer 1

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

This is fixed by casting the first argument to text:

SELECT COALESCE(NULL::numeric::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],
    ' ', '')
  )