List partition for remaining items

mysql, oracle

Solution

Unlike range partitioning, there is no "catch-all" in list partitioning. To quote the documentation:

Unlike the case with RANGE partitioning, there is no “catch-all” such as MAXVALUE; all expected values for the partitioning expression should be covered in PARTITION ... VALUES IN (...) clauses. An INSERT statement containing an unmatched partitioning column value fails with an error...

Unfortunately, I believe, you're not able to combine a list and a range partition either. I'm not entirely certain why you would want to use a list partition in this particular instance; wouldn't a range partition work just a well?

CREATE TABLE tbl
(
 ID integer
)
PARTITION BY RANGE (ID)
(  
  PARTITION P1 values less than (2),  
  PARTITION P2 values less than (3),  
  PARTITION P3 values less than (4),  
  PARTITION Pother values less than maxvalue
);

I'm assuming that this is just an example. Partitioning each key in a primary key is a little pointless.

Problem

when partitioning tables in MySQL using a list, how can I generate a partition for remaining items? E.G: ``` CREATE TABLE tbl ( ID integer ) PARTITION BY LIST (ID) ( PARTITION P1 values in (1), PARTITION P2 values in (2), PARTITION P3 values in (3), PARTITION Pother values in (<all remaining values of ID>) ); ``` In Oracle, I use `values in (default)`, but that does work in MySQL.

Original source