Обсуждение: 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