On Tue, Apr 24, 2012 at 08:12:10AM +1000, Chris Angelico wrote: > On Tue, Apr 24, 2012 at 6:49 AM, Nick Apperson <apperson@xxxxxxxxx> wrote: > > There are obviously workarounds for this, but I'm wondering why > > the following query shouldn't work. It seems like it should. With > > MVCC already present on the back-end, I can't see any reason other > > than additional parsing routines that this couldn't work: > > > > INSERT INTO old_login_id_to_new_account_id(new_account_id, > > old_login_id) INSERT INTO accounts(id, username, password_hash, > > email) SELECT DEFAULT, username, password_hash, email FROM > > logins_old RETURNING id, logins_old.id; > > That's possible using WITH. I made a statement that creates an > invoice and its lines (with the lines all having a foreign-key > reference to the owning invoice) more or less the same way: > > WITH inv AS (insert into ... returning id), constants AS (values > (...),(...),(...)) INSERT INTO invoicelines (columnlist) SELECT > inv.id,constants.* FROM inv,constants > > Something like that. I do remember running into trouble with the > multi-row insert (can't use multiple rows of literals with SELECT, > and can't fetch data from a WITH expression with VALUES), so it had > to go to the extra level of structure. If you're inserting just one > row into each, this should be easy. > > Of course, the question I never asked (never bothered to, really) > was: Is it really any better than simply doing the first insert and > retrieving the ID in my application? :) One crucial difference is the number of round trips to the database. Cheers, David. -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
