Re: [SQL] insert from a select
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
> 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
Re: [SQL] insert from a select
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
> 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
>
> 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 ([email protected])
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>>
>
Re: [SQL] insert from a select
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
>
> > 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
> >
> > 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] insert from a select
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 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
> >
> > > 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
> > >
> > > 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Regards,
--
Trinath Somanchi,
[SQL] error null value in column" concat_id" violates not-null constraint
Hi, I'm just beggining in PostgreSql 9.0 I've created a table ,like this: CREATE TABLE auxiliar ( ano integer, codigodc character varying, id character varying, concat_id character varying NOT NULL, CONSTRAINT concat PRIMARY KEY (concat_id); Now i want to insert values on concat_id resulting from a function called "concat_id" INSERT INTO concat_id SELECT(concat_id) FROM auxiliar ; I get "error null value in column"concat_id" violatres not null constraint Could anyone tell me what am i doing wrong? Ana -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] error null value in column" concat_id" violates not-null constraint
Hello 2010/11/25 Ana Louro : > Hi, > I'm just beggining in PostgreSql 9.0 > > I've created a table ,like this: > > CREATE TABLE auxiliar > ( > ano integer, > codigodc character varying, > id character varying, > concat_id character varying NOT NULL, > CONSTRAINT concat PRIMARY KEY (concat_id); > > Now i want to insert values on concat_id resulting from a function > called "concat_id" > > INSERT INTO concat_id > SELECT(concat_id) FROM auxiliar ; insert has a syntax: INSERT INTO function call has a syntax funcname(parameters) so if I would to fill table auxiliar from some function, then I'll use a statement INSERT INTO auxilar SELECT * FROM funcname(..) Regards Pavel Stehule > > I get "error null value in column"concat_id" violatres not null > constraint > > > > Could anyone tell me what am i doing wrong? > > Ana > > > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
