0

I need to truncate a partition in Greenplum based on a condition. What would be the easiest possible way to achieve that?

Example:

ALTER TABLE PACKAGE_INFO TRUNCATE PARTITION FOR ('BOX');

I need to truncate the partition with value - BOX in the table PACKAGE_INFO only for a particular condition. Say only for few PACKAGE_IDs.

Some thing like, truncate the partition containing PACKAGE_TYPE data for PACKAGE_ID - 100,200,300. I am not inclined towards DELETE because if I use delete, I won't be able to compress the table.

Thanks.

ntalbs
  • 28,700
  • 8
  • 66
  • 83
Deepak
  • 53
  • 1
  • 7

1 Answers1

0

In GPDB 4.3.x you can delete from AO and compressed table. You should upgrade as 4.2.x would be EOL in 2 months and you would no longer have a support

Truncate command in databases is used to drop the whole table/partition without validation and data scan, this is why it is fast. In GPDB it just alters the pg_class with different relfilenode and deletes the table file from the underlying filesystem. There is no option to partially truncate the table data

If you are still on 4.2.x you have to do something more smart. One option is to recreate partition without this data. Second option is to have a separate "visibility map" table that would contain the list of deleted PACKAGE_IDs, each time you would query the table you would have to join with this table to remove the "deleted" records

0x0FFF
  • 4,948
  • 3
  • 20
  • 26