Thread: insert from a select

Hi,I have a strange issue that is mostly likely me not understanding something.  
I always thought that an insert statement would accept any select statement.  
I'm guessing I am wrong.

I have created a temporary table ("tempclass") that is exact match to an 
existing table ('esclass').

When I attempt to do the following
insert into tempclass Select cl.pkid, cl.depart, cl.sessionid, cl.instrid, 
cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid) as 
facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)

I get the following error:

ERROR:  column "schedule" is of type date but expression is of type character 
varying
LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa...
             ^
 
HINT:  You will need to rewrite or cast the expression.

The error makes no sense to me.  But most important if I just run the select 
statement it works perfectly.

Like I said the table "tempclass" (a temporary) is a dup of table "esclass" so 
none of it makes sense.  Of course I did say I'm missing something.

So why isn't the select statement working with the insert?

Johnf

On Wednesday 24 November 2010 4:07:43 pm John Fabiani wrote:
> Hi,
>  I have a strange issue that is mostly likely me not understanding
> something. I always thought that an insert statement would accept any
> select statement. I'm guessing I am wrong.
>
> I have created a temporary table ("tempclass") that is exact match to an
> existing table ('esclass').
>
> When I attempt to do the following
> insert into tempclass Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
> as facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
>
> I get the following error:
>
> ERROR:  column "schedule" is of type date but expression is of type
> character varying
> LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa...
>                                                              ^
> HINT:  You will need to rewrite or cast the expression.
>
> The error makes no sense to me.  But most important if I just run the
> select statement it works perfectly.
>
> Like I said the table "tempclass" (a temporary) is a dup of table "esclass"
> so none of it makes sense.  Of course I did say I'm missing something.
>
> So why isn't the select statement working with the insert?

Looks like an off by one situation. See error detail below:

LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa..
            ^
 

Looks like the result of the 'select facility.." is being inserted into the 
schedule column. 

>
> Johnf



-- 
Adrian Klaver
adrian.klaver@gmail.com

Try to explicit the column names. Something like:<br /><br />insert into tempclass (pkid, depart, sessionid,
instrid)<br/>Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,<br /> cl.classseq,(select facility from esloc where
esloc.pkid= cl.locationid) as<br /> facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)<br /><br
/><divclass="gmail_quote">2010/11/24 John Fabiani <span dir="ltr"><<a
href="mailto:johnf@jfcomputer.com">johnf@jfcomputer.com</a>></span><br/><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Hi,<br />  I have a
strangeissue that is mostly likely me not understanding something.<br /> I always thought that an insert statement
wouldaccept any select statement.<br /> I'm guessing I am wrong.<br /><br /> I have created a temporary table
("tempclass")that is exact match to an<br /> existing table ('esclass').<br /><br /> When I attempt to do the
following<br/> insert into tempclass Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,<br /> cl.classseq,(select
facilityfrom esloc where esloc.pkid = cl.locationid) as<br /> facility, cl.schedule from esclass cl where cl.pkid in
(14507,14508)<br/><br /> I get the following error:<br /><br /> ERROR:  column "schedule" is of type date but
expressionis of type character<br /> varying<br /> LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select
fa...<br/>                                                             ^<br /> HINT:  You will need to rewrite or cast
theexpression.<br /><br /> The error makes no sense to me.  But most important if I just run the select<br /> statement
itworks perfectly.<br /><br /> Like I said the table "tempclass" (a temporary) is a dup of table "esclass" so<br />
noneof it makes sense.  Of course I did say I'm missing something.<br /><br /> So why isn't the select statement
workingwith the insert?<br /><br /> Johnf<br /><font color="#888888"><br /><br /><br /><br /><br /> --<br /> Sent via
pgsql-sqlmailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to
yoursubscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/><br /></font></blockquote></div><br /> 

On Thursday, November 25, 2010 04:32:57 am Carla wrote:
> Sorry. I forgot some columns:
> 
> insert into tempclass (pkid, depart, sessionid, instrid, *classeq,
> facility, schedule*)
> Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
> as facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
> 
> 2010/11/25 Carla <cgourofino@hotmail.com>
> 
> > Try to explicit the column names. Something like:
> > 
> > insert into tempclass (pkid, depart, sessionid, instrid)
> > 
> > Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> > cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
> > as
> > facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
> > 
> > 2010/11/24 John Fabiani <johnf@jfcomputer.com>
> > 
> > Hi,
> > 
> >>  I have a strange issue that is mostly likely me not understanding
> >> 
> >> something.
> >> I always thought that an insert statement would accept any select
> >> statement.
> >> I'm guessing I am wrong.
> >> 
> >> I have created a temporary table ("tempclass") that is exact match to an
> >> existing table ('esclass').
> >> 
> >> When I attempt to do the following
> >> insert into tempclass Select cl.pkid, cl.depart, cl.sessionid,
> >> cl.instrid, cl.classseq,(select facility from esloc where esloc.pkid =
> >> cl.locationid) as
> >> facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
> >> 
> >> I get the following error:
> >> 
> >> ERROR:  column "schedule" is of type date but expression is of type
> >> character
> >> varying
> >> LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select
> >> fa...
> >> 
> >>                                                             ^
> >> 
> >> HINT:  You will need to rewrite or cast the expression.
> >> 
> >> The error makes no sense to me.  But most important if I just run the
> >> select
> >> statement it works perfectly.
> >> 
> >> Like I said the table "tempclass" (a temporary) is a dup of table
> >> "esclass" so
> >> none of it makes sense.  Of course I did say I'm missing something.
> >> 
> >> So why isn't the select statement working with the insert?
> >> 
> >> Johnf

Thanks to all  - after reading everyone responses I slept on it.  Today I 
realized that the order of the fields mattered as you all suggested.  

Thanks to all,
Johnf