[FEATURE] Support deleting partitions without dropping parent table

[popomaro516]

Describe the feature

Support for deleting partitions without dropping the parent table.

Currently, when attempting to delete a partition while keeping the parent table, pg-schema-diff returns an error:

Error: generating plan: generating plan statements: generating migration statements:
resolving table diff: generating delete statements for "public"."prtimes_media_outlet_release_log_2025_02":
generating sql: deleting partitions without dropping parent table: not implemented

Expected behavior:

pg-schema-diff should generate a DROP TABLE statement for the partition when:

• A partition exists in the old schema
• The partition is removed in the new schema
• The parent table still exists in both schemas

Example:

-- Old schema
CREATE TABLE parent_table (...) PARTITION BY RANGE (created_at);
CREATE TABLE partition_2025_01 PARTITION OF parent_table FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE partition_2025_02 PARTITION OF parent_table FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- New schema (dropping old partition)
CREATE TABLE parent_table (...) PARTITION BY RANGE (created_at);
CREATE TABLE partition_2025_02 PARTITION OF parent_table FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

Expected migration output:

DROP TABLE partition_2025_01;

Motivation

In time-based partitioning strategies (e.g., monthly or yearly partitions), it's common to drop old partitions to manage data retention policies while keeping the parent table active for ongoing operations.

Currently, we must manually drop partitions outside of pg-schema-diff, which breaks the declarative schema management workflow and requires manual intervention for routine partition maintenance.

According to the code comment in sql_generator.go:860-861:

Don't support dropping partitions without dropping the base table. This would be easy to implement, but we would need to add tests for it.

This suggests the implementation is straightforward and mainly requires test coverage.

Contribution

I'm interested in contributing this feature. Would a pull request be welcome?

The implementation would involve:

1. Removing the restriction in sql_generator.go:860-866 that returns ErrNotImplemented when deleting partitions without dropping the parent table
2. Generating DROP TABLE statement for the partition with appropriate hazard warnings (MigrationHazardTypeDeletesData)
3. Adding test coverage in partitioned_table_cases_test.go following the existing test pattern

Please let me know if this aligns with the project's roadmap and if there are any specific requirements I should consider.