# Partition Management - Adding Partitions

+ **`RANGE`**, **`RANGE COLUMNS`**, **`LIST`**, and **`LIST COLUMNS`**
```12
ALTER TABLE <table_name> ADD PARTITION (<partition specification>)
```

+ **`HASH`** and **`KEY`**
```12
ALTER TABLE <table_name> ADD PARTITION <number of partitions to be added>
or
ALTER TABLE <table_name> ADD PARTITION <new partition definitions>
```
------------------------------------------------------------------------------
For `RANGE`, `RANGE COLUMNS`, `LIST`, and `LIST COLUMNS` partitioned tables, we use `ALTER TABLE <table_name> ADD PARTITION (<partition specification>)` to add partitions, for example:
```
tidb> CREATE TABLE subscribers (
 -> sub_id INT NOT NULL,
 -> firstname VARCHAR(25) NOT NULL,
 -> lastname VARCHAR(25) NOT NULL,
 -> journalid INT NOT NULL,
 -> joined DATE NOT NULL
 -> )
 -> PARTITION BY RANGE(YEAR(joined)) (
 -> PARTITION p0 VALUES LESS THAN (2000),
 -> PARTITION p1 VALUES LESS THAN (2010),
 -> PARTITION p2 VALUES LESS THAN (2020),
 -> PARTITION p3 VALUES LESS THAN (2030)
 -> );
Query OK, 0 rows affected (0.53 sec)

tidb> SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+-------------+------------------------------+
| TABLE_NAME  | GROUP_CONCAT(PARTITION_NAME) |
+-------------+------------------------------+
| subscribers | p0,p1,p2,p3                  |
+-------------+------------------------------+
1 row in set (0.01 sec)

tidb> ALTER TABLE subscribers ADD PARTITION (PARTITION `p5` VALUES LESS THAN (2040));
Query OK, 0 rows affected (0.53 sec)

tidb> SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+-------------+------------------------------+
| TABLE_NAME  | GROUP_CONCAT(PARTITION_NAME) |
+-------------+------------------------------+
| subscribers | p0,p1,p2,p3,p5               |
+-------------+------------------------------+
1 row in set (0.01 sec)
```

For a table that is partitioned by `RANGE`, using `ADD PARTITION` will add new partitions after the last existing one. When creating new partitions, the value specified in `VALUES LESS THAN` must be greater than those defined for the existing partitions. Otherwise, an error is reported:

```
tidb> CREATE TABLE subscribers (
 -> sub_id INT NOT NULL,
 -> firstname VARCHAR(25) NOT NULL,
 -> lastname VARCHAR(25) NOT NULL,
 -> journalid INT NOT NULL,
 -> joined DATE NOT NULL
 -> )
 -> PARTITION BY RANGE( YEAR(joined) ) (
 -> PARTITION p0 VALUES LESS THAN (2000),
 -> PARTITION p1 VALUES LESS THAN (2010),
 -> PARTITION p2 VALUES LESS THAN (2020),
 -> PARTITION p3 VALUES LESS THAN (2030)
 -> );
Query OK, 0 rows affected (0.54 sec)

tidb> ALTER TABLE subscribers ADD PARTITION (PARTITION `p5` VALUES LESS THAN (1990));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
```

For a `RANGE` partitioned table, the last partition definition must not be defined as `MAXVALUE`. For example:
```
tidb> CREATE TABLE subscribers (
 -> sub_id INT NOT NULL,
 -> firstname VARCHAR(25) NOT NULL,
 -> lastname VARCHAR(25) NOT NULL,
 -> journalid INT NOT NULL,
 -> joined DATE NOT NULL
 -> )
 -> PARTITION BY RANGE( YEAR(joined) ) (
 -> PARTITION p0 VALUES LESS THAN (2000),
 -> PARTITION p1 VALUES LESS THAN (2010),
 -> PARTITION p2 VALUES LESS THAN (2020),
 -> PARTITION p3 VALUES LESS THAN (2030),
 -> PARTITION pm VALUES LESS THAN MAXVALUE
 -> );
Query OK, 0 rows affected (0.54 sec)

tidb> SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+-------------+------------------------------+
| TABLE_NAME | GROUP_CONCAT(PARTITION_NAME)  |
+-------------+------------------------------+
| subscribers | p0,p1,p2,p3,pm               |
+-------------+------------------------------+
1 row in set (0.02 sec)

tidb> ALTER TABLE subscribers ADD PARTITION (PARTITION `p5` VALUES LESS THAN (2040));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
```

For `HASH` and `KEY` partitioned tables, there are two ways to add partitions, like:

```
tidb> CREATE TABLE users (
 -> uid INT NOT NULL,
 -> uname VARCHAR(25) NOT NULL,
 -> ucity VARCHAR(25) NOT NULL,
 -> addr VARCHAR(25) NOT NULL,
 -> ulogin DATE NOT NULL DEFAULT CURRENT_DATE()
 -> )
 -> PARTITION BY KEY(uid, ucity)
 -> PARTITIONS 4;
Query OK, 0 rows affected (0.54 sec)

tidb> SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='users' GROUP BY TABLE_NAME;
+------------+------------------------------+
| TABLE_NAME | GROUP_CONCAT(PARTITION_NAME) |
+------------+------------------------------+
| users      | p0,p1,p2,p3                  |
+------------+------------------------------+
1 row in set (0.02 sec)

tidb> ALTER TABLE users ADD PARTITION PARTITIONS 2;
Query OK, 0 rows affected, 1 warning (3.03 sec)

tidb> SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='users' GROUP BY TABLE_NAME;
+------------+------------------------------+
| TABLE_NAME | GROUP_CONCAT(PARTITION_NAME) |
+------------+------------------------------+
| users      | p0,p1,p2,p3,p4,p5            |
+------------+------------------------------+
1 row in set (0.01 sec)

tidb> ALTER TABLE users ADD PARTITION
 -> (PARTITION extp1 COMMENT = 'extp1 instead',
 -> PARTITION extp2 COMMENT = 'extp2 instead');
Query OK, 0 rows affected, 1 warning (6.04 sec)

tidb> SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='users' GROUP BY TABLE_NAME;
+------------+-------------------------------+
| TABLE_NAME | GROUP_CONCAT(PARTITION_NAME)  |
+------------+-------------------------------+
| users      | p0,p1,p2,p3,p4,p5,extp1,extp2 |
+------------+-------------------------------+
1 row in set (0.01 sec)
```