Обсуждение: INSERT INTO problem
Hello all, I'm trying to do an INSERT INTO using a UNION of two selects. I received an error: "ERROR: Unable to convert varchar to int2 for column isdepartment" I started simplifying the INSERT INTO to help me get to the root cause. I removed the UNION and continued simplifying. I eventually ended up with this line that still gives me the error: insert into reportentity select 'D0'||text(departmentid) as reportentityid, departmentname as reportentityname, '1'::int2 as isdepartment, departmentdescription as reportentitydescription, departmentsummary as reportentitysummary from department where isreportentity > 0; Now removing the last two fields lets the INSERT INTO run without errors: insert into reportentity select 'D0'||text(departmentid) as reportentityid, departmentname as reportentityname, '1'::int2 as isdepartment from department where isreportentity > 0; Some of the description and summary fields do have null values; is that causing the error? (I'm not sure why it would but that's all I can think of) Oh here's the format of the reportentity table: reportentityid char(5) not null reportentityname varchar(110) not null reportentitydescription varchar(4000) reportentitysummary varchar(4000) isdepartment int2 any help would be appreciated. Tom Jenkins devIS - Development Infostructure http://www.devis.com
Did you try removing the quotes from '1'::int2 as isdepartment so that it looks: insert into reportentity select 'D0'||text(departmentid) as reportentityid, departmentname as reportentityname, 1 as isdepartment, departmentdescription as reportentitydescription, departmentsummary as reportentitysummary from department where isreportentity > 0; Tom Jenkins wrote: > Hello all, > I'm trying to do an INSERT INTO using a UNION of two selects. I > received an error: > "ERROR: Unable to convert varchar to int2 for column isdepartment" > > I started simplifying the INSERT INTO to help me get to the root cause. > I removed the UNION and continued simplifying. > > I eventually ended up with this line that still gives me the error: > insert into reportentity > select 'D0'||text(departmentid) as reportentityid, > departmentname as reportentityname, > '1'::int2 as isdepartment, > departmentdescription as reportentitydescription, > departmentsummary as reportentitysummary > from department where isreportentity > 0; > > Now removing the last two fields lets the INSERT INTO run without errors: > insert into reportentity > select 'D0'||text(departmentid) as reportentityid, > departmentname as reportentityname, > '1'::int2 as isdepartment > from department where isreportentity > 0; > > Some of the description and summary fields do have null values; is that > causing the error? (I'm not sure why it would but that's all I can > think of) > > Oh here's the format of the reportentity table: > reportentityid char(5) not null > reportentityname varchar(110) not null > reportentitydescription varchar(4000) > reportentitysummary varchar(4000) > isdepartment int2 > > any help would be appreciated. > > Tom Jenkins > devIS - Development Infostructure > http://www.devis.com
Heh, I originally had that in the message then deleted it as possible noise. My original insert had 1 as isdepartment I then migrated to 1::int2 as isdepartment and finally to the version I had posted. All returned the same error, unfortunately. Alfonso Peniche wrote: > Did you try removing the quotes from '1'::int2 as isdepartment > so that it looks: > > insert into reportentity > select 'D0'||text(departmentid) as reportentityid, > departmentname as reportentityname, > 1 as isdepartment, > departmentdescription as reportentitydescription, > departmentsummary as reportentitysummary > from department where isreportentity > 0; > > > Tom Jenkins wrote: > >> Hello all, >> I'm trying to do an INSERT INTO using a UNION of two selects. I >> received an error: >> "ERROR: Unable to convert varchar to int2 for column isdepartment" >> >> Tom Jenkins devIS - Development Infostructure http://www.devis.com
On Tue, 20 Feb 2001, Tom Jenkins wrote: > Hello all, > I'm trying to do an INSERT INTO using a UNION of two selects. I > received an error: > "ERROR: Unable to convert varchar to int2 for column isdepartment" > > I started simplifying the INSERT INTO to help me get to the root cause. > I removed the UNION and continued simplifying. > > I eventually ended up with this line that still gives me the error: > insert into reportentity > select 'D0'||text(departmentid) as reportentityid, > departmentname as reportentityname, > '1'::int2 as isdepartment, > departmentdescription as reportentitydescription, > departmentsummary as reportentitysummary > from department where isreportentity > 0; I do not believe that it uses the aliases you give to say which columns those are associated with. I think you need to have the select items in the same order as the columns, so, it's erroring trying to convert a departmentsummary value to an int2. Reordering the columns should help.
Stephan Szabo wrote: > On Tue, 20 Feb 2001, Tom Jenkins wrote: > >> Hello all, >> I'm trying to do an INSERT INTO using a UNION of two selects. I >> received an error: >> "ERROR: Unable to convert varchar to int2 for column isdepartment" >> >> I started simplifying the INSERT INTO to help me get to the root cause. >> I removed the UNION and continued simplifying. >> >> I eventually ended up with this line that still gives me the error: >> insert into reportentity >> select 'D0'||text(departmentid) as reportentityid, >> departmentname as reportentityname, >> '1'::int2 as isdepartment, >> departmentdescription as reportentitydescription, >> departmentsummary as reportentitysummary >> from department where isreportentity > 0; > > > I do not believe that it uses the aliases you give to say which columns > those are associated with. I think you need to have the select items > in the same order as the columns, so, it's erroring trying to convert > a departmentsummary value to an int2. Reordering the columns should > help. You are correct! I rearranged the columns, matching the order of the table, and the INSERT INTO ... UNION... works now. Thank you very much. Tom Jenkins devIS - Development Infostructure http://www.devis.com
Tom Jenkins <tjenkins@devis.com> writes: > I eventually ended up with this line that still gives me the error: > insert into reportentity > select 'D0'||text(departmentid) as reportentityid, > departmentname as reportentityname, > '1'::int2 as isdepartment, > departmentdescription as reportentitydescription, > departmentsummary as reportentitysummary > from department where isreportentity > 0; > Oh here's the format of the reportentity table: > reportentityid char(5) not null > reportentityname varchar(110) not null > reportentitydescription varchar(4000) > reportentitysummary varchar(4000) > isdepartment int2 It looks like the order of the columns in the table doesn't match the order of the SELECT outputs. The 'AS' labels you're sticking on the SELECT don't have anything to do with how the system will match things up. If you want to write the column values in the select in an order different than they're declared in the table, you must do insert into reportentity (reportentityid, reportentityname, isdepartment, ...) select 'D0'|| ... regards, tom lane