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