Re: INSERT INTO problem

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