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:

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