Thread: Can't cast from char to integer...

Hi all.

I'm trying to cut data from one table and put it in another table.  The
problem comes from the fact that the first table has a field that is a
char(x) and the destination table needs that data in an integer.

For example, I'm trying to do this:

insert into data
    select cast('666' as integer) as block_number, phone as phone_number, name
from demo_q;

The data table has a field called block_number that is an integer.  I'm trying
to populate that field with the INTEGER, 666.  (I use 666 for testing since I
would never assign that number/id to an actuall customer.)

When I run this query, I get:

ERROR:  column "block_number" is of type integer but expression is of type
character varying

What am I doing wrong?

TIA,
--
Mike Diehl

Mike Diehl <mdiehl@diehlnet.com> writes:
> For example, I'm trying to do this:

> insert into data
>     select cast('666' as integer) as block_number, phone as phone_number, name
> from demo_q;

> When I run this query, I get:

> ERROR:  column "block_number" is of type integer but expression is of type
> character varying

I don't get that ...

I wonder whether the problem is that you are expecting the "as" labels
in the SELECT to do something, like make the data go into the
correspondingly named columns of the target table.  But they don't do
anything.  This query is going to try to assign 666, phone, and name to
the first three columns of "data", in order.  I'll bet it is actually
complaining about the second or third column, not the 666 at all.

If that's it, the syntax you are looking for is

insert into data (block_number, phone_number, name)
    select cast('666' as integer), phone_number, name
from demo_q;

            regards, tom lane

That fixed it.  If you are ever in Albuquerque, NM., let me know.  I'll be
happy to buy you a beer.

Mike.


On Monday 29 September 2008 05:26:43 pm Tom Lane wrote:
> Mike Diehl <mdiehl@diehlnet.com> writes:
> > For example, I'm trying to do this:
> >
> > insert into data
> >     select cast('666' as integer) as block_number, phone as phone_number,
> > name from demo_q;
> >
> > When I run this query, I get:
> >
> > ERROR:  column "block_number" is of type integer but expression is of
> > type character varying
>
> I don't get that ...
>
> I wonder whether the problem is that you are expecting the "as" labels
> in the SELECT to do something, like make the data go into the
> correspondingly named columns of the target table.  But they don't do
> anything.  This query is going to try to assign 666, phone, and name to
> the first three columns of "data", in order.  I'll bet it is actually
> complaining about the second or third column, not the 666 at all.
>
> If that's it, the syntax you are looking for is
>
> insert into data (block_number, phone_number, name)
>     select cast('666' as integer), phone_number, name
> from demo_q;
>
>             regards, tom lane



--
Mike Diehl

On Mon, Sep 29, 2008 at 8:02 PM, Mike Diehl <mdiehl@diehlnet.com> wrote:
> That fixed it.  If you are ever in Albuquerque, NM., let me know.  I'll be
> happy to buy you a beer.
>

Tom probably has enough beers coming to him that he could found a new
software company with money from returning the bottles for the
deposit.

merlin

On Tue, Sep 30, 2008 at 10:46 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Sep 29, 2008 at 8:02 PM, Mike Diehl <mdiehl@diehlnet.com> wrote:
>> That fixed it.  If you are ever in Albuquerque, NM., let me know.  I'll be
>> happy to buy you a beer.
>>
>
> Tom probably has enough beers coming to him that he could found a new
> software company with money from returning the bottles for the
> deposit.

Tom will never lack for pizza or beer when he's visiting Denver...

On Tuesday 30 September 2008 10:46:46 am Merlin Moncure wrote:
> On Mon, Sep 29, 2008 at 8:02 PM, Mike Diehl <mdiehl@diehlnet.com> wrote:
> > That fixed it.  If you are ever in Albuquerque, NM., let me know.  I'll
> > be happy to buy you a beer.
>
> Tom probably has enough beers coming to him that he could found a new
> software company with money from returning the bottles for the
> deposit.
>
> merlin

Yes, and rightfully so.  Offer still stands.

Take care and have fun.
--
Mike Diehl