Re: [GENERAL] [PGSQL 8.2.x] INSERT+INSERT
On Thursday 21 June 2007 15:47:17 Dawid Kuroczko wrote:
On 6/21/07, Vincenzo Romano [EMAIL PROTECTED] wrote:
Hi all.
I'd like to do the following:
insert into t1
values (
'atextvalue',(
insert into t2
values ( 'somethingelse' )
returning theserial
)
)
;
that is, I first insert data into t2 getting back the newly
created serial values, then i insert this values in another
table. I get an error message:
ERROR: syntax error at or near into
referring to thwe second inner into.
Is there a way to do this?
The inner insert...returning should be the expression to be
used in the outer insert.
My objective iDawid Kuroczko [EMAIL PROTECTED]s to create an
SQL script to load some 20+ million
records and avoiding function calls would save some time.
I'm afraid INSERT ... RETURNING cannot be used where a (sub)select
could be. It returns data to the calling application only.
I think it would be greatly helpful if the insert...returning could be
seen as a select statement and, thus, being usable in the way I
have described. I suspect that the insert...returning is actually
implemented as an inser plus a select.
Given tables:
qnex=# CREATE TABLE t1 (t text, id int);
qnex=# CREATE TABLE t2 (id serial, sth text);
NOTICE: CREATE TABLE will create implicit sequence t2_id_seq for
serial column t2.id
You want to:
qnex=# INSERT INTO t2 (sth) VALUES ('somethingelse');
INSERT 0 1
qnex=# INSERT INTO t1 VALUES ('atextvalue', currval('t2_id_seq'));
INSERT 0 1
This works only when you execute statements one by one like you did.
Or wrap it around SQL function:
That's the way I'm doind now even if in a slightly different way.
20+ million calls will badly slooow down the DB insertions and require
you to know the exact name of the implicit sequence and, more
important, not to have any concurrent accesses to it.
--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [PGSQL 8.2.x] INSERT+INSERT
I think it would be greatly helpful if the insert...returning could be seen as a select statement and, thus, being usable in the way I have described. I suspect that the insert...returning is actually implemented as an inser plus a select. You can create a function which does the INSERT RETURNING and returns the result, and use this function in a subsequent INSERT. You can also write, in plpgsql : FOR a,b,c IN SELECT ... LOOP INSERT INTO table1 (...) VALUES (a,b) RETURNING your_id INTO _var; INSERT INTO table2 (...) VALUES (c , _var ); END LOOP ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [PGSQL 8.2.x] INSERT+INSERT
Vincenzo Romano [EMAIL PROTECTED] writes: Or wrap it around SQL function: That's the way I'm doind now even if in a slightly different way. 20+ million calls will badly slooow down the DB insertions Are you sure? How much? and require you to know the exact name of the implicit sequence If you're sure you only have a single sequence being bumped here (no triggers inserting somewhere else, or other columns with sequences for default values, etc) then you could use lastval(). and, more important, not to have any concurrent accesses to it. huh? no, sequences don't require this. They would be pointless if they did. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] [PGSQL 8.2.x] INSERT+INSERT
Hi all. I'd like to do the following: insert into t1 values ( 'atextvalue',( insert into t2 values ( 'somethingelse' ) returning theserial ) ) ; that is, I first insert data into t2 getting back the newly created serial values, then i insert this values in another table. I get an error message: ERROR: syntax error at or near into referring to thwe second inner into. Is there a way to do this? The inner insert...returning should be the expression to be used in the outer insert. My objective is to create an SQL script to load some 20+ million records and avoiding function calls would save some time. Thanks in advance. -- Vincenzo Romano -- Maybe Computer will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1988] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [PGSQL 8.2.x] INSERT+INSERT
On 6/21/07, Vincenzo Romano [EMAIL PROTECTED] wrote:
Hi all.
I'd like to do the following:
insert into t1
values (
'atextvalue',(
insert into t2
values ( 'somethingelse' )
returning theserial
)
)
;
that is, I first insert data into t2 getting back the newly created
serial values, then i insert this values in another table.
I get an error message:
ERROR: syntax error at or near into
referring to thwe second inner into.
Is there a way to do this?
The inner insert...returning should be the expression to be used in
the outer insert.
My objective is to create an SQL script to load some 20+ million
records and avoiding function calls would save some time.
I'm afraid INSERT ... RETURNING cannot be used where a (sub)select
could be. It returns data to the calling application only.
Given tables:
qnex=# CREATE TABLE t1 (t text, id int);
qnex=# CREATE TABLE t2 (id serial, sth text);
NOTICE: CREATE TABLE will create implicit sequence t2_id_seq for
serial column t2.id
You want to:
qnex=# INSERT INTO t2 (sth) VALUES ('somethingelse');
INSERT 0 1
qnex=# INSERT INTO t1 VALUES ('atextvalue', currval('t2_id_seq'));
INSERT 0 1
Or wrap it around SQL function:
qnex=# CREATE OR REPLACE FUNCTION t_insert(sth text, t text) RETURNS VOID AS $$
INSERT INTO t2 (sth) VALUES ($1);
INSERT INTO t1 (t,id) VALUES ($2, currval('t2_id_seq'));
$$ LANGUAGE SQL;
CREATE FUNCTION
qnex=# SELECT t_insert('foo', 'bar');
...which should be inlined nicely, without PL/PgSQL overhead.
Regards,
Dawid
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
