How to create a new column and partition table on new column

[mlake-nyt]

Hello, I'd like to alter an existing table that is partitioned on update_time, by inserting a new column, insertion_time, and partitioning off of insertion_time. It looks like if I want to partition on a different column, I need to create a new table (please correct me if I'm wrong). So I

1. create all new things: a new table with insertion_time and a new template
2. partition on insertion_time with pg partman via create_parent()
3. migrate the data from the old table to the new table via INSERT INTO new table from OLD TABLE
4. DROP TABLE IF EXISTS old_table CASCADE; drop table old_template
5. rename the foreign keys, rename the new table to the old table, rename template (in that order)
   However, my partitioned child tables do not rename. How do I rename the partitioned tables new_table_name_p2025_02, etc to old_table_name_p2025_02 via pg_partman?

[keithf4]

Yes, you will have to migrate to a new table. Everything looks good there.

You'll have to manually rename all the child tables. You can use some catalog querying techniques to generate all the rename statements. See this document for migrating to pg_partman for some examples - https://github.com/pgpartman/pg_partman/blob/development/doc/migrate_to_partman.md

[keithf4]

If you want partman to manage it, you will either have to run create_parent() on the new table or manually add an entry for it into the part_config table. The issue they ran into with running into an error should be resolved. You also don't have to worry about triggers anymore since that's all handled by native partitioning now.

[mlake-nyt]

when I re-run create_parent() after renaming the table and after renaming the child partition tables, I run into this error * migration failed: relation <renamed-default-partition-table> already exists CONTEXT: SQL statement "CREATE TABLE public.<renamed-default-partition-table> (LIKE public.<renamed-parent-table> INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS INCLUDING GENERATED )"
There's no data in the default partition table so I tried DROP TABLE public.<renamed-default-partition-table>; but ran into a table does not exist error. same issue if I try to rename the default partition table instead. any ideas appreciated!

[mlake-nyt]

I tried setting p_default_table => false when I re-run create_parent but I then get this error. It looks like it is not accepting p_default_table as a field

migration failed: function create_parent(p_parent_table => unknown, p_control => unknown, p_type => unknown, p_interval => unknown, p_template_table => unknown, p_default_table => boolean) does not exist

[keithf4]

Setting p_default_table => false should allow that to work for now. I'll see about making it not care if the given default table already exists.

I'd have to see your entire call to be able to tell you why you're getting that error. I can pretty much guarantee you it's syntax error somewhere in the command though. PG never gets that error without a valid reason I've seen. Is pg_partman in a schema and you forgot to schema qualify your call? I've done that quite often myself to get that same error.

[mlake-nyt]

hmm strange. i wonder if it has to do with the version of pg partman we're using. I ended up removing the create_parent() call for the renamed table so the old table gets dropped from pg partman. but this was the call i was failing on when I tried to initially add the table back into pg partman. the renamed table will get dropped, but I had wanted to add it back into pg partman bc i thought the data migration process from the old table to the new table would be smoother that way

DO $recreate$
    BEGIN
        IF EXISTS(
            SELECT extname FROM pg_extension
            WHERE extname = 'pg_partman')
        THEN
            PERFORM create_parent(
                    p_parent_table => 'public.my_old_table',
                    p_control      => 'my_col',
                    p_type         => 'native',
                    p_interval     => 'monthly',
                    p_template_table => 'public.my_old_table_template',
                    p_default_table => false
                    );

            UPDATE public.part_config
            SET retention            = '30 days',
                retention_keep_table = false,
                infinite_time_partitions = true
            WHERE parent_table = 'public.my_old_table';
            CALL public.run_maintenance_proc();
        ELSE
            RAISE NOTICE 'pg partman not installed';
        END IF;
    END
$recreate$;