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,
somethinglike:
>
> 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
duplicatesin 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
shouldhave been a UNION and not a UNION ALL, or you get every record twice. As an alternative you could add a fictive
columnto each RETURNING statement to specify the origin of each record. That all depends on what you need the results
forof 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
peoplewho 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!