Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mike Diehl <mdiehl(at)diehlnet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Can't cast from char to integer...
Date: 2008-09-29 23:26:43
Message-ID: 25861.1222730803@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Mike Diehl <mdiehl(at)diehlnet(dot)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

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2008-09-29 23:31:32
Subject: Re: database question
Previous:From: Joshua DrakeDate: 2008-09-29 23:26:28
Subject: Re: pg_start_backup() takes too long

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group