pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
INSERT and SELECT
9+ messages / 8 participants
[nested] [flat]

* INSERT and SELECT
@ 1999-12-10 15:46  Eric BARROCA <eric@netsystems.fr>
  0 siblings, 4 replies; 9+ messages in thread

From: Eric BARROCA @ 1999-12-10 15:46 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I've tried this query with PostgreSQL, but it doesn't work. Could anyone
explain me why and what must I do to get the same result ?
Query example : "INSERT INTO table VALUES (
			(SELECT oid FROM membre WHERE email="toto@toto.com"),
			"test");"
I get the error : "parse error near select".

I've been searched in the manual and with deja.com but I haven't found
something about this.
This query works with mysql (and it's the only one query that works with
this RDBMS ;-)


Thanks,

Éric.

-- 
Éric BARROCA               | Logiciels Libres, Conception Intranet/Extranet,
E-Mail: eric@netsystems.fr | Sites Web dynamiques, Commerce électronique,
Tel: +33 (0)2 48 21 54 67  | Formations (Unix/Linux, Perl, etc.), Réseaux... 
GSM: +33 (0)6 16 35 33 25  | NetSystems : <http://www.netsystems.fr;



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] INSERT and SELECT
@ 1999-12-10 16:31  Stuart Rison <rison@biochemistry.ucl.ac.uk>
  parent: Eric BARROCA <eric@netsystems.fr>
  3 siblings, 2 replies; 9+ messages in thread

From: Stuart Rison @ 1999-12-10 16:31 UTC (permalink / raw)
  To: Eric BARROCA <eric@netsystems.fr>; +Cc: pgsql-sql

If you go into psql and type \h INSERT or \h SELECT you'll get the syntax
for these:

test_capture=> \h INSERT
Command: insert
Description: insert tuples
Syntax:
        INSERT INTO class_name [(attr1, ...attrN)]
        VALUES (expr1,..exprN) |
        SELECT [DISTINCT [ON attrN]]
        expr1, ...exprN
        [FROM from_clause]
        [WHERE qual]
        [GROUP BY group_list]
        [HAVING having_clause]
        [UNION [ALL] SELECT ...];   

So what you want is:

INSERT INTO table VALUES SELECT oid, 'test' FROM membre WHERE
email="toto@toto.com";

Assuming table 'table' already exists.

Or 

test_capture=> \h SELECT
Command: select
Description: retrieve tuples
Syntax:
        SELECT [DISTINCT [ON attrN]] expr1 [AS attr1], ...exprN
        [INTO [TABLE] class_name]
        [FROM from_list]
        [WHERE qual]
        [GROUP BY group_list]
        [HAVING having_clause]
        [ORDER BY attr1 [ASC|DESC] [USING op1], ...attrN ]
        [UNION [ALL] SELECT ...];      

SELECT oid, 'test' INTO other_table FROM membre WHERE
email="toto@toto.com");

which will create the appropriate funtion for you.

HTH,

S. 


On 10 Dec 1999, Eric BARROCA wrote:

> Hi,
> 
> I've tried this query with PostgreSQL, but it doesn't work. Could anyone
> explain me why and what must I do to get the same result ?
> Query example : "INSERT INTO table VALUES (
> 			(SELECT oid FROM membre WHERE email="toto@toto.com"),
> 			"test");"
> I get the error : "parse error near select".
> 
> I've been searched in the manual and with deja.com but I haven't found
> something about this.
> This query works with mysql (and it's the only one query that works with
> this RDBMS ;-)
> 
> 
> Thanks,
> 
> Éric.
> 
> -- 
> Éric BARROCA               | Logiciels Libres, Conception Intranet/Extranet,
> E-Mail: eric@netsystems.fr | Sites Web dynamiques, Commerce électronique,
> Tel: +33 (0)2 48 21 54 67  | Formations (Unix/Linux, Perl, etc.), Réseaux... 
> GSM: +33 (0)6 16 35 33 25  | NetSystems : <http://www.netsystems.fr;
> 
> ************
> 

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: rison@biochem.ucl.ac.uk




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] INSERT and SELECT
@ 1999-12-10 16:38  Vladimir Terziev <vlady@school.digsys.bg>
  parent: Eric BARROCA <eric@netsystems.fr>
  3 siblings, 0 replies; 9+ messages in thread

From: Vladimir Terziev @ 1999-12-10 16:38 UTC (permalink / raw)
  To: Eric BARROCA <eric@netsystems.fr>; +Cc: pgsql-sql


    Type

   database=>\h select 


     Vladimir





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] INSERT and SELECT
@ 1999-12-10 16:43  Nikolay Mijaylov <nmmm@nmmm.nu>
  parent: Eric BARROCA <eric@netsystems.fr>
  3 siblings, 1 reply; 9+ messages in thread

From: Nikolay Mijaylov @ 1999-12-10 16:43 UTC (permalink / raw)
  To: Eric BARROCA <eric@netsystems.fr>; +Cc: pgsql-sql

As i know PgSQL support subqueryes only in Where part
--------------------------------------------------------------
The reboots are for hardware upgrades!
"http://www.nmmm.nu; <nmmm@nmmm.nu>

----- Original Message -----
From: Eric BARROCA <eric@netsystems.fr>
To: <pgsql-sql@postgreSQL.org>
Sent: ïåòúê, Äåêåìâðè 10, 1999 05:46
Subject: [SQL] INSERT and SELECT


> Hi,
>
> I've tried this query with PostgreSQL, but it doesn't work. Could anyone
> explain me why and what must I do to get the same result ?
> Query example : "INSERT INTO table VALUES (
> (SELECT oid FROM membre WHERE email="toto@toto.com"),
> "test");"
> I get the error : "parse error near select".
>
> I've been searched in the manual and with deja.com but I haven't found
> something about this.
> This query works with mysql (and it's the only one query that works with
> this RDBMS ;-)
>
>
> Thanks,
>
> Éric.
>
> --
> Éric BARROCA               | Logiciels Libres, Conception
Intranet/Extranet,
> E-Mail: eric@netsystems.fr | Sites Web dynamiques, Commerce électronique,
> Tel: +33 (0)2 48 21 54 67  | Formations (Unix/Linux, Perl, etc.),
Réseaux...
> GSM: +33 (0)6 16 35 33 25  | NetSystems : <http://www.netsystems.fr;
>
> ************




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] INSERT and SELECT
@ 1999-12-10 16:49  Eric BARROCA <eric@netsystems.fr>
  parent: Stuart Rison <rison@biochemistry.ucl.ac.uk>
  1 sibling, 1 reply; 9+ messages in thread

From: Eric BARROCA @ 1999-12-10 16:49 UTC (permalink / raw)
  To: Stuart Rison <rison@biochemistry.ucl.ac.uk>; +Cc: pgsql-sql

Stuart Rison <rison@biochemistry.ucl.ac.uk> writes:
> INSERT INTO table VALUES SELECT oid, 'test' FROM membre WHERE
> email="toto@toto.com";
I've tried it, but it doesn't work too ("paser error near or at select"),
even if I replace " by '.

The sample table "table" :
	- ID int4
	- login varchar()
	- password varchar()

I'd like to put into the ID culumn the result of (SELECT ...), into the
login column "test" and into the password one "testpass".

I've tried many queries like :

INSERT INTO table (id, login, password) VALUES SELECT oid, 'test',
'testpass' FROM membre WHERE email='toto@toto.com';

or

INSERT INTO table (id, login, password) VALUES ((SELECT oid FROM membre
WHERE email='toto@toto.com'), 'test', 'testpass'); 

or 

INSERT INTO table (id, login, password) VALUES (SELECT oid, 'test',
'testpass' FROM membre WHERE email='toto@toto.com');

...

But nothing give me a result. I get always parse error near select, and I
can't figure why.


Best Regards,

Éric.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] INSERT and SELECT
@ 1999-12-10 16:54  Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
  parent: Stuart Rison <rison@biochemistry.ucl.ac.uk>
  1 sibling, 0 replies; 9+ messages in thread

From: Ross J. Reedstrom @ 1999-12-10 16:54 UTC (permalink / raw)
  To: Eric BARROCA <eric@netsystems.fr>; pgsql-sql

I wasn't going to respond to this, but there are two, almost correct, replies,
so I thought I should edit a bit:

On Fri, Dec 10, 1999 at 04:31:32PM +0000, Stuart Rison wrote:
> If you go into psql and type \h INSERT or \h SELECT you'll get the syntax
> for these:

Good idea ;-)

> 
> test_capture=> \h INSERT
> Command: insert
> Description: insert tuples
> Syntax:
>         INSERT INTO class_name [(attr1, ...attrN)]
>         VALUES (expr1,..exprN) |
>         SELECT [DISTINCT [ON attrN]]
>         expr1, ...exprN
>         [FROM from_clause]
>         [WHERE qual]
>         [GROUP BY group_list]
>         [HAVING having_clause]
>         [UNION [ALL] SELECT ...];   
> 
> So what you want is:
> 
> INSERT INTO table VALUES SELECT oid, 'test' FROM membre WHERE
> email="toto@toto.com";

Oops, the | is between the VALUES and SELECT clauses, so that should be:

INSERT INTO table SELECT oid, 'test' FROM membre WHERE
email='toto@toto.com';

Note that the other followup, about quote marks, is also correct.

> 
> 
> Assuming table 'table' already exists.
> 
> Or 
> 
> test_capture=> \h SELECT
> Command: select
> Description: retrieve tuples
> Syntax:
>         SELECT [DISTINCT [ON attrN]] expr1 [AS attr1], ...exprN
>         [INTO [TABLE] class_name]
>         [FROM from_list]
>         [WHERE qual]
>         [GROUP BY group_list]
>         [HAVING having_clause]
>         [ORDER BY attr1 [ASC|DESC] [USING op1], ...attrN ]
>         [UNION [ALL] SELECT ...];      
> 
> SELECT oid, 'test' INTO other_table FROM membre WHERE
> email="toto@toto.com");

SELECT oid, 'test' INTO other_table FROM membre WHERE
email='toto@toto.com');

Just quote marks, here.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] INSERT and SELECT
@ 1999-12-10 16:57  Jan Wieck <wieck@debis.com>
  parent: Eric BARROCA <eric@netsystems.fr>
  0 siblings, 0 replies; 9+ messages in thread

From: wieck@debis.com @ 1999-12-10 16:57 UTC (permalink / raw)
  To: eric@netsystems.fr; +Cc: rison@biochemistry.ucl.ac.uk; pgsql-sql

>
> Stuart Rison <rison@biochemistry.ucl.ac.uk> writes:
> > INSERT INTO table VALUES SELECT oid, 'test' FROM membre WHERE
> > email=3D"toto@toto.com";
> I've tried it, but it doesn't work too ("paser error near or at select"),
> even if I replace " by '.

    Because  VALUES  and  SELECT  are  mutually exclusive at that
    point.

    Just do INSERT INTO table SELECT ...

    or INSERT INTO TABLE (attrib, ...) SELECT ...

    and the parser should be happy.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] INSERT and SELECT
@ 1999-12-10 17:12  neko@kredit.sth.szif.hu
  parent: Nikolay Mijaylov <nmmm@nmmm.nu>
  0 siblings, 0 replies; 9+ messages in thread

From: neko@kredit.sth.szif.hu @ 1999-12-10 17:12 UTC (permalink / raw)
  To: Nikolay Mijaylov <nmmm@nmmm.nu>; +Cc: Eric BARROCA <eric@netsystems.fr>; pgsql-sql

On Fri, 10 Dec 1999, Nikolay Mijaylov wrote:

> As i know PgSQL support subqueryes only in Where part
subquery? ;)
No.
> > I've tried this query with PostgreSQL, but it doesn't work. Could anyone
> > explain me why and what must I do to get the same result ?
> > Query example : "INSERT INTO table VALUES (
> > (SELECT oid FROM membre WHERE email="toto@toto.com"),
> > "test");"
Try:
=> INSERT INTO table 
 -- NO values, but
-> SELECT oid FROM membre WHERE  ... ;
it may works.

--
 nek;(




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] INSERT and SELECT
@ 1999-12-10 17:23  kaiq@realtyideas.com
  parent: Eric BARROCA <eric@netsystems.fr>
  3 siblings, 0 replies; 9+ messages in thread

From: kaiq@realtyideas.com @ 1999-12-10 17:23 UTC (permalink / raw)
  To: Eric BARROCA <eric@netsystems.fr>; +Cc: pgsql-sql

change the double quotation mark into sigle ones:
email='toto@toto.com',
'test'

this is sql92 also, so, it's not pgsql's fault ;-).

On 10 Dec 1999, Eric BARROCA wrote:

> Hi,
> 
> I've tried this query with PostgreSQL, but it doesn't work. Could anyone
> explain me why and what must I do to get the same result ?
> Query example : "INSERT INTO table VALUES (
> 			(SELECT oid FROM membre WHERE email="toto@toto.com"),
> 			"test");"
> I get the error : "parse error near select".
> 
> I've been searched in the manual and with deja.com but I haven't found
> something about this.
> This query works with mysql (and it's the only one query that works with
> this RDBMS ;-)
> 
> 
> Thanks,
> 
> Éric.
> 
> -- 
> Éric BARROCA               | Logiciels Libres, Conception Intranet/Extranet,
> E-Mail: eric@netsystems.fr | Sites Web dynamiques, Commerce électronique,
> Tel: +33 (0)2 48 21 54 67  | Formations (Unix/Linux, Perl, etc.), Réseaux... 
> GSM: +33 (0)6 16 35 33 25  | NetSystems : <http://www.netsystems.fr;
> 
> ************
> 




^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~1999-12-10 17:23 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-10 15:46 INSERT and SELECT Eric BARROCA <eric@netsystems.fr>
1999-12-10 16:31 ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-12-10 16:49   ` Eric BARROCA <eric@netsystems.fr>
1999-12-10 16:57     ` Jan Wieck <wieck@debis.com>
1999-12-10 16:54   ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-12-10 16:38 ` Vladimir Terziev <vlady@school.digsys.bg>
1999-12-10 16:43 ` Nikolay Mijaylov <nmmm@nmmm.nu>
1999-12-10 17:12   ` neko@kredit.sth.szif.hu
1999-12-10 17:23 ` kaiq@realtyideas.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