title: "invalid input syntax for type numeric" for entering "emptyness"
body: <p>I have a table with three columns using the NUMERIC type. However, two of them have nothing (ie. empty). Here it's the code:</p>
  
  <pre><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,''));
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>UPDATE profiles SET nr_traces = 10131, trace_space_m = 12.5 WHERE PROFILE = '30';
  </code></pre>
  
  <p>and got:</p>
  
  <pre><code>ERROR:  invalid input syntax for type numeric: ""
  </code></pre>
  
  <p>and</p>
  
  <pre><code>ERROR:  tsvector column "TS" does not exist
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>coalesce(my-numeric-empty-column,'')
  coalesce(my-numeric-empty-column,'')::numeric
  </code></pre>
  
  <p>if I test the populated LENGTH_M column alone, I get the same pointing to the '' (empty) space:</p>
  
  <pre><code>psql:profiles.sql:146: ERROR:  invalid input syntax for type numeric: ""
  LINE 1: ... ' ' || TRACE_SPACE_M || ' ' || coalesce(LENGTH_M,'') || ' '...
                                                           ^
  </code></pre>
  
  <p>How can I circumvent this without populating in the first place these two empty columns?</p>
  
  <p>I'd appreciate any hints on this, thanks in advance.</p>