On 11 Sep 2010, at 12:09, Alban Hertroys wrote: > It would be great to be able to use a WITH statement to lock down a data set > for multiple subsequent operations, something like: > > WITH nonduplicates (key, data1, data2, etc) AS ( > SELECT key, data1, data2, etc FROM staging_table > EXCEPT > SELECT key, data1, data2, etc FROM live_table > ) > INSERT INTO live_table (key, data1, data2, etc) > SELECT key, data1, data2, etc FROM nonduplicates > RETURNING key, data1, data2, etc > UNION ALL > DELETE FROM staging_table USING nonduplicates > WHERE key = nonduplicates.key > RETURNING key, data1, data2, etc; > > Or something like that. It's just an example from what I have in mind, after > all ;)
Gosh, I was thinking too far ahead and forgot to explain why that would be cool! First off, you'd end up with having moved all your non-duplicate data into the live_table and are left with all the duplicates in your staging_table. No need for an extra table to store them! Secondly, you get a list returned of all the non-duplicate records that were moved into the live_table. I realise that should have been a UNION and not a UNION ALL, or you get every record twice. As an alternative you could add a fictive column to each RETURNING statement to specify the origin of each record. That all depends on what you need the results for of course... I think the RETURNING clauses are pretty much obligatory there, how else would you UNION that INSERT and DELETE together? Lastly, of course this is already entirely possible using a temp table, but that seems a bit ugly... Big kudos to the people who added WITH-queries to Postgres, I love that feature! :) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c8b5a5710401646614364! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
