Re: Suggestion: provide a "TRUNCATE PARTITION" command

Michael Lewis schrieb am 08.01.2021 um 16:32:
> On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net>> wrote:
>
>     Hello,
>
>     I wonder if it made sense to add a "TRUNCATE PARTITION" command to Postgres?
>
>     Especially during bulk loads it's more efficient to TRUNCATE a partition if I know I want to replace all rows,
ratherthan doing a DELETE. 
>
>     Currently this requires dynamic SQL which isn't always feasible (and might get complicated quickly).
>
>     So I was thinking that a new command to allow truncating partitions by identifying the partitions by "value"
ratherby name might be helpful in that case. 
>
>     Something along the lines of:
>
>           truncate partitions of base_table
>           for values in (...);
>
>     If the IN part allowed for sub-queries then this could be used to gather the partition keys from e.g. a staging
table.
>
>
> For me, it seems too easily error prone such that a single typo in
> the IN clause may result in an entire partition being removed that
> wasn't supposed to be targeted.

I don't see how this is more dangerous then:

     delete from base_table
     where partition_key in (...);

which would serve the same purpose, albeit less efficient.

> Given the user still needs to
> manually generate that list somehow, I don't see it as a huge effort
> to query the partitions and run individual commands to truncate or
> detach several partitions manually.

Well, the list could come from e.g. a staging table, e.g. "for values IN (select some_column from staging_table)"