pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT 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