Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.

On 07/06/2012 09:45 PM, Stanislaw Pankevich wrote:

> Question: Is there a possibility in PostgreSQL to do DELETE on many 
> tables massively, like TRUNCATE allows. Like DELETE table1, table2, ...? 

Yes, you can do it with a writable common table expression, but you 
wanted version portability.

WITH
   discard1 AS (DELETE FROM test1),
   discard2 AS (DELETE FROM test2 AS b)
SELECT 1;

Not only will this not work in older versions (IIRC it only works with 
9.1, maybe 9.0 too but I don't see it in the documentation for SELECT 
for 9.0) but I find it hard to imagine any performance benefit over 
simply sending

   DELETE FROM test1; DELETE FROM test2;

This all smells like premature optimisation of cases that don't matter. 
What problem are you solving with this?

--
Craig Ringer