# RANGE Partitioning Type
The `RANGE` partitioning type is used to assign each partition a range of values generated by the partitioning expression. Ranges must be ordered, contiguous and non-overlapping. The minimum value is always included in the first range. The highest value may or may not be included in the last range.
A variant of this partitioning method, [RANGE COLUMNS](https://mariadb.com/docs/server/server-usage/partitioning-tables/partitioning-types/range-columns-and-list-columns-partitioning-types), allows us to use multiple columns and more datatypes.
## Syntax
The last part of a [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) statement can be definition of the new table's partitions. In the case of `RANGE` partitioning, the syntax is the following:
```sql
PARTITION BY RANGE (partitioning_expression)
(
PARTITION partition_name VALUES LESS THAN (value),
[ PARTITION partition_name VALUES LESS THAN (value), ... ]
[ PARTITION partition_name VALUES LESS THAN MAXVALUE ]
)
```
`PARTITION BY RANGE` indicates that the partitioning type is `RANGE`.
* *`partitioning_expression`* is an SQL expression that returns a value from each row. In the simplest cases, it is a column name. This value is used to determine which partition should contain a row.
* *`partition_name`* is the name of a partition.
* *`value`* indicates the upper bound for that partition. The values must be ascending. For the first partition, the lower limit is `NULL`. When trying to insert a row, if its value is higher than the upper limit of the last partition, the row are rejected (with an error, if the [IGNORE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/ignore) keyword is not used).
As a catchall, `MAXVALUE` can be specified as a value for the last partition. Note, however, that in order to append a new partition, it is not possible to use [ADD PARTITION](https://mariadb.com/docs/server/server-usage/partitioning-overview#adding-partitions); instead, [REORGANIZE PARTITION](https://mariadb.com/docs/server/server-usage/partitioning-overview#splitting-partitions) must be used.
## Use Cases
A typical use case is when we want to partition a table whose rows refer to a moment or period in time; for example commercial transactions, blog posts, or events of some kind. We can partition the table by year, to keep all recent data in one partition and distribute historical data in big partitions that are stored on slower disks. Or, if our queries always read rows which refer to the same month or week, we can partition the table by month or year week (in this case, historical data and recent data are stored together).
[AUTO\_INCREMENT](https://mariadb.com/docs/server/reference/data-types/auto_increment) values also represent a chronological order. So, these values can be used to store old data in separate partitions. However, partitioning by id is not the best choice if we usually query a table by date.
## Examples
Partitioning a log table by year:
```sql
CREATE TABLE log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
dt DATETIME NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, dt)
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
```
Partitioning the table by both year and month:
```sql
CREATE TABLE log2
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ts TIMESTAMP NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, ts)
)
ENGINE = InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(ts))
(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01 00:00:00'))
);
```
In the last example, the [UNIX\_TIMESTAMP](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/unix_timestamp) function is used to accomplish the purpose. Also, the first two partitions cover longer periods of time (probably because the logged activities were less intensive).
In both cases, when our tables become huge and we don't need to store all historical data any more, we can drop the oldest partitions in this way:
```sql
ALTER TABLE log DROP PARTITION p0;
```
We will still be able to drop a partition that does not contain the oldest data, but all rows stored in it will disappear.
Example of an error when inserting outside a defined partition range:
```sql
INSERT INTO log(id,dt) VALUES
(1, '2016-01-01 01:01:01'),
(2, '2015-01-01 01:01:01');
ERROR 1526 (HY000): Table has no partition for value 2016
```
To avoid the error, use the `IGNORE` keyword:
```sql
INSERT IGNORE INTO log(id,dt) VALUES
(1, '2016-01-01 01:01:01'),
(2, '2015-01-01 01:01:01');
SELECT * FROM log;
+----+---------------------+------+
| id | timestamp | user |
+----+---------------------+------+
| 2 | 2015-01-01 01:01:01 | NULL |
+----+---------------------+------+
```
An alternative definition with `MAXVALUE` as a catchall:
```sql
CREATE TABLE log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
dt DATETIME NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, dt)
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
```
*This page is licensed: CC BY-SA / Gnu FDL*
{% @marketo/form formId="4316" %}
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://mariadb.com/docs/server/server-usage/partitioning-tables/partitioning-types/range-partitioning-type.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.