On Sunday, October 21, 2012 07:24:52 PM Andrew Dunstan wrote: > On 10/21/2012 12:36 PM, Andres Freund wrote: > > On Sunday, October 21, 2012 06:30:14 PM Andrew Dunstan wrote: > >> On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote: > >>> At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote: > >>>> If there is a natural sequence (e.g. - a value assigned by nextval()), > >>>> that offers a natural place to apply the usual order-imposing ORDER BY > >>>> that we are expected to use elsewhere. > >>> > >>> Note: "INSERT … RETURNING" doesn't accept an ORDER BY clause. > >> > >> No, but you can wrap the INSERT .. RETURNING in a CTE and order that. > > > > Personally I find that a not very practical suggestion. It means you need > > the ability to sort the data equivalently on the clientside which isn't > > always easy if you consider platform/locale and whatever differences. > > Er, what? > > with orig_inserts as > ( > insert into table_1 > ... > returning * > ), > ordered_inserts as > ( > select * from orig_inserts > order by ... > ) > insert into table_2 > select * from ordered_inserts ...;
I am not sure I get the point of this. > why does the client have to be involved, exactly? 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. Am I missing something here? Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers