Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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: ""
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.
                It looks like you are updating a numeric column, not a tsvector column. Also, to refer to an uppercase TS name in SQL, you need  double quotes, e.g. "TS".
– Mike T
                Nov 24, 2013 at 22:20
                @MikeToews thanks Mike for the reply. Well in fact the ts column is being populated through an update statement. Please refer to my question for more info, I recently updated it.
– Gery
                Nov 24, 2013 at 22:56
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
  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],
    ' ', '')
                thanks Mike, even though this works, if I update these numeric columns with UPDATE profiles SET nr_traces=10131, trace_space_m=12.5 WHERE PROFILE = '93-30'; this still appears tsvector column "TS" does not exist, so I cannot anymore update those columns with numbers?
– Gery
                Nov 25, 2013 at 7:33
                just to point that I have a trigger for insert or updates like: 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);, here it's the problem I think
– Gery
                Nov 25, 2013 at 7:37
                The column name needs to be 'ts' since postgres is case-sensitive. Or, you could rename the column name to "TS", but it is annoying in the long run to always double-quote things.
– Mike T
                Nov 25, 2013 at 11:30
                I changed to 'ts' but the problem still occurs. I run again the script but without the trigger, after that the update worked correctly, so the trigger is the problem, is there a way to adapt the trigger or something in it?
– Gery
                Nov 25, 2013 at 11:37
                I tried recently NR_TRACES::numeric in the trigger but gave a syntax error at or near "::"
– Gery
                Nov 25, 2013 at 11:42
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.