Re: [PERFORM] insert without oids
On Fri, Jan 13, 2006 at 04:29:15PM -0500, Neil Conway wrote:
There's really no additional operations required:
INSERT INTO t2 VALUES (currval('t1_id_seq'), ...);
You need a separate SELECT if you want to use the generated sequence
value outside the database,
That would, of course, be the goal. IOW, if you have a table which has
data which is unique only for the serial column, the old syntax provided
a way to refer to the newly inserted row uniquely without any additional
operations.
although the INSERT ... RETURNING extension will avoid that
That sounds promising. I'll have to put the TODO list on my todo list.
:)
Mike Stone
---(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: [PERFORM] insert without oids
On Fri, 2006-01-13 at 15:10 -0500, Michael Stone wrote:
> OIDs seem to be on their way out, and most of the time you can get a
> more helpful result by using a serial primary key anyway, but I wonder
> if there's any extension to INSERT to help identify what unique id a
> newly-inserted key will get? Using OIDs the insert would return the OID
> of the inserted row, which could be useful if you then want to refer to
> that row in a subsequent operation. You could get the same result by
> manually retrieving the next number in the sequence and using that value
> in the insert, but at the cost of additional DB operations.
There's really no additional operations required:
INSERT INTO t1 VALUES (...);
INSERT INTO t2 VALUES (currval('t1_id_seq'), ...);
You need a separate SELECT if you want to use the generated sequence
value outside the database, although the INSERT ... RETURNING extension
will avoid that (there's a patch implementing this, although it is not
yet in CVS).
-Neil
---(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: [PERFORM] insert without oids
On Fri, Jan 13, 2006 at 03:10:11PM -0500, Michael Stone wrote: > Are there plans on updating the insert API for the post-OID world? Are you looking for this TODO item? * Allow INSERT/UPDATE ... RETURNING new.col or old.col This is useful for returning the auto-generated key for an INSERT. One complication is how to handle rules that run as part of the insert. http://www.postgresql.org/docs/faqs.TODO.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
