That's strange. Even with the multiple levels it doesn't seem like the new partitioning should exceed the 64K combined partition limit for 2-byte partitioning.
You may have to create a new table and copy the data.
Thanks for that. I get the same error as you but I can't see why.
With your additional partitions you only need 23568 partitions, so still well inside the 2-byte partitioning limit.
I've tried creating a table with your new partitioning scheme and that works without issue.
Unless anyone else can spot the 'deliberate mistake' then I think you'll have to raise an incident with TD.
To get your table working with the new date range, is it feasible to build a new table which has the required (new) partitioning?
Sorry I couldn't be more help.
It's such a large table, that I was trying to avoid rebuilding if at all possible. However, I'm running some tests on our other environment just to get some idea of the time that would take.
I do really appreciate both of you looking at it with me. I think I will raise an incident with Teradata.
New ranges can be added (or dropped) only on the top level.
When you want ADD n rangen in any other level you must also DROP n ranges, e.g. add 12 new months and drop the 12 oldest months. The number of partitions must remain the same, otherwise internal partition numbers would change and all rows would be updated because PARTITION is part of the ROWID.
Since a few releases there's a new option to reserve a number of partitions for future use
PARTITION BY ( CASE_N(...), RANGE_N(...) ADD 1000 );
Now you could add 1000 partitions later.
For this existing PPI there's no way to ALTER the table, you must Insert/Select into a new table.
Of course, don't use the ADD feature, simply use DATE '2029-06-30' as range end (no overhead for empty partitions) to keep the 2-byte partitioning and avoid doing it again in the near future.