From: John Fabiani <johnf@jfcomputer.com>
Organization: J.F. Computer Solutions
To: pgsql-sql@postgresql.org
Subject: insert from a select
Date: Wed, 24 Nov 2010 16:07:43 -0800
User-Agent: KMail/1.13.5 (Linux/2.6.34.7-0.5-desktop; KDE/4.4.4; i686; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201011241607.43378.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