Re: Best possible way to insert and get returned ids

On Mon, Nov 23, 2009 at 1:53 PM, Jason Dictos <jdictos(at)barracuda(dot)com> wrote:
> Question:
>
> Is an INSERT command with a SELECT statement in the RETURNING * parameter
> faster than say an INSERT and then a SELECT? Does the RETURNING * parameter
> simply amount to a normal SELECT command on the added rows? We need to
> basically insert a lot of rows as fast as possible, and get the ids that
> were added.  The number of rows we are inserting is dynamic and is not of
> fixed length.

Well, if you do an insert, then a select, how can you tell, with that
select, which rows you just inserted? how can you be sure they're not
somebody elses?

Insert returning is fantastic for this type of thing. The beauty of
it is that it returns a SET if you insert multiple rows. And, if
you've got two insert threads running, and one inserts to a sequence a
set of rows with pk values of 10,11,13,15,18,20 while another thread
inserts to the same table and creates a set of rows with pk values of
12,14,16,17,19 then those are the two sets you'll get back with
returning.