pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinsert from a select
6+ messages / 4 participants
[nested] [flat]
* insert from a select
@ 2010-11-25 00:07 John Fabiani <johnf@jfcomputer.com>
0 siblings, 2 replies; 6+ messages in thread
From: John Fabiani @ 2010-11-25 00:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insert from a select
@ 2010-11-25 00:25 Adrian Klaver <adrian.klaver@gmail.com>
parent: John Fabiani <johnf@jfcomputer.com>
1 sibling, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2010-11-25 00:25 UTC (permalink / raw)
To: pgsql-sql; +Cc: John Fabiani <johnf@jfcomputer.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insert from a select
@ 2010-11-25 11:40 Carla <cgourofino@hotmail.com>
parent: John Fabiani <johnf@jfcomputer.com>
1 sibling, 1 reply; 6+ messages in thread
From: Carla @ 2010-11-25 11:40 UTC (permalink / raw)
To: pgsql-sql; +Cc: John Fabiani <johnf@jfcomputer.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
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insert from a select
@ 2010-11-25 12:32 Carla <cgourofino@hotmail.com>
parent: Carla <cgourofino@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Carla @ 2010-11-25 12:32 UTC (permalink / raw)
To: pgsql-sql; +Cc: John Fabiani <johnf@jfcomputer.com>
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
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insert from a select
@ 2010-11-25 13:33 John Fabiani <johnf@jfcomputer.com>
parent: Carla <cgourofino@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: John Fabiani @ 2010-11-25 13:33 UTC (permalink / raw)
To: cgourofino@hotmail.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: insert from a select
@ 2010-11-25 15:22 Trinath Somanchi <trinath.somanchi@gmail.com>
parent: John Fabiani <johnf@jfcomputer.com>
0 siblings, 0 replies; 6+ messages in thread
From: Trinath Somanchi @ 2010-11-25 15:22 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: cgourofino@hotmail.com; pgsql-sql
Is the query stated in the mail a working on.
Can we give a query for a column name in the SELECT statement.
"
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)
"
On Thu, Nov 25, 2010 at 7:03 PM, John Fabiani <johnf@jfcomputer.com> wrote:
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Regards,
----------------------------------------------
Trinath Somanchi,
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-11-25 15:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-25 00:07 insert from a select John Fabiani <johnf@jfcomputer.com>
2010-11-25 00:25 ` Adrian Klaver <adrian.klaver@gmail.com>
2010-11-25 11:40 ` Carla <cgourofino@hotmail.com>
2010-11-25 12:32 ` Carla <cgourofino@hotmail.com>
2010-11-25 13:33 ` John Fabiani <johnf@jfcomputer.com>
2010-11-25 15:22 ` Trinath Somanchi <trinath.somanchi@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox