Обсуждение: INSERT INTO problem

Поиск
Список
Период
Сортировка

INSERT INTO problem

От
Tom Jenkins
Дата:
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


Re: INSERT INTO problem

От
Alfonso Peniche
Дата:
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


Re: Re: INSERT INTO problem

От
Tom Jenkins
Дата:
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



Re: INSERT INTO problem

От
Stephan Szabo
Дата:
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.


Re: INSERT INTO problem

От
Tom Jenkins
Дата:
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


Re: INSERT INTO problem

От
Tom Lane
Дата:
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