Thread: insert without oids

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. Are there
plans on updating the insert API for the post-OID world?

Mike Stone

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

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

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