Re: [SQL] INSERT and SELECT

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(at)toto(dot)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(at)toto(dot)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(at)toto(dot)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(at)netsystems(dot)fr | Sites Web dynamiques, Commerce lectronique,
> Tel: +33 (0)2 48 21 54 67 | Formations (Unix/Linux, Perl, etc.), Rseaux...
> 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(at)biochem(dot)ucl(dot)ac(dot)uk