Thread: cast needed - but where and why?
Hello list, I am getting the following error after upgrading from 7.4.6 to 8.3.6 and can't figure out what is wrong. Any help would be greatly appreciated. from our program: sqlcode=-400 errmsg='column "event_ref_log_no" is of type integer but expression is of type text' in line 4138. from pg_log: 2009-04-02 10:45:10 EDT:srm2api:ERROR: column "event_ref_log_no" is of type integer but expression is of type text at character146 2009-04-02 10:45:10 EDT:srm2api:HINT: You will need to rewrite or cast the expression. 2009-04-02 10:45:10 EDT:srm2api:STATEMENT: insert into t_unit_event_log ( event_log_no , unit_serial_no , event_type , event_category , event_mesg , event_severity , event_status , event_ref_log_no , event_logged_by , event_date , alarm , last_updated_by , last_updated_date ) values ( nextval ( 'seq_event_log_no' ) , $1 , $2 , $3 , $4 , $ 5 , $6 , case when $7 > 0 then $8 else null end , current_user , now () , $9 , current_user , now () ) from our program: exec sql begin declare section; int h_cnt = 0; int h_event_ref_log_no = NULL; ... // insert into uel exec sql insert into t_unit_event_log (event_log_no, unit_serial_no, event_type, event_category, event_mesg, event_severity, event_status, event_ref_log_no, event_logged_by, event_date, alarm, last_updated_by, last_updated_date) values (nextval('seq_event_log_no'), :h_serial_no, :h_type, :h_category, :h_mesg, :h_sev, :h_status, case when :h_event_ref_log_no > 0 then :h_event_ref_log_no else null end, current_user, now(), :h_alarm, current_user, now()); Thanks, Steve
Steve Clark <sclark@netwolves.com> writes: > I am getting the following error after upgrading from 7.4.6 to 8.3.6 > and can't figure out what is wrong. Any help would be greatly appreciated. > 2009-04-02 10:45:10 EDT:srm2api:ERROR: column "event_ref_log_no" is of type integer but expression is of type text atcharacter 146 I don't know ecpg very well, but if it doesn't provide any information about parameter datatypes then the backend would resolve this: case when $7 > 0 then $8 else null end as producing a result of type "text". 7.4 would have allowed that to be cast to int silently, but 8.3 won't (and the runtime cast involved would've been expensive anyway). I suggest sticking a cast directly on the ambiguous parameter, ie > case when :h_event_ref_log_no > 0 > then :h_event_ref_log_no :: integer > else null end, (You needn't cast the null, since the type attached to the other case arm is a sufficient cue.) regards, tom lane
Tom Lane wrote: > Steve Clark <sclark@netwolves.com> writes: >> I am getting the following error after upgrading from 7.4.6 to 8.3.6 >> and can't figure out what is wrong. Any help would be greatly appreciated. >> 2009-04-02 10:45:10 EDT:srm2api:ERROR: column "event_ref_log_no" is of type integer but expression is of type text atcharacter 146 > > I don't know ecpg very well, but if it doesn't provide any information > about parameter datatypes then the backend would resolve this: > > case when $7 > 0 then $8 else null end > > as producing a result of type "text". 7.4 would have allowed that to be > cast to int silently, but 8.3 won't (and the runtime cast involved > would've been expensive anyway). I suggest sticking a cast directly > on the ambiguous parameter, ie > >> case when :h_event_ref_log_no > 0 >> then :h_event_ref_log_no :: integer >> else null end, > > (You needn't cast the null, since the type attached to the other case > arm is a sufficient cue.) > > regards, tom lane > Thanks Tom, that fixed the problem. I wasn't thinking about what the back end was seeing, only that it was defined in my pgc program as an int. Regards, Steve