Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows

On Sunday, October 21, 2012 08:45:31 PM Andrew Dunstan wrote:
> On 10/21/2012 01:40 PM, Andres Freund wrote:
> > Suppose you have something like
> > 
> > CREATE TABLE positionlog(
> > id serial primary key,
> > timestamp timestamptz DEFAULT NOW(),
> > position geometry
> > );
> > 
> > And you want to insert multiple values in one roundtrip *and* know their
> > ids in your application.
> > 
> > INSERT INTO positionlog(position)
> > VALUES
> > 
> >      ('POINT(..., ...)'),
> >      ('POINT(..., ...)')
> > 
> > RETURNING id, timestamp, position
> > ;
> > 
> > If you want to correlate re returned ids with data in your application
> > without relying on the ordering of INSERT ... VALUES... RETURNING you
> > would need to sort a postgis type in the same way the server does it.
> 
> I see. Sorry, I should not have joined the thread late in the piece
> while I'm multitasking.
> 
> I guess in such a case I'd be inclined to precompute the id values and
> then supply them in the values clause. That means two round trips rather
> than one.

Which will fail should we get upsert one day...

Andres

-- 
Andres Freund        http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services