# `LIST COLUMNS` Partition - Example

+ One or more columns as partitioning keys
+ The data types of partition columns can be integer, string (**`CHAR`** or **`VARCHAR`**), **`DATE`**, and **`DATETIME`**
+ Expressions, such as **`YEAR()`**, are not supported

```
CREATE TABLE users (
 uid INT NOT NULL,
 uname VARCHAR(25) NOT NULL,
 ucity VARCHAR(50) NOT NULL,
 addr VARCHAR(25) NOT NULL
)
~!@PARTITION BY LIST COLUMNS (ucity)@!~(
PARTITION p0 VALUES IN ('Seattle','Seoul','Tokyo','Sydney'),
PARTITION p1 VALUES IN ('Berlin', 'Cairo', 'Madrid'),
PARTITION p2 VALUES IN ('Vancouver', 'Ottawa', ‘Chicago')
); 

CREATE TABLE gusers (
 uid INT NOT NULL,
 ugroupid INT NOT NULL,
 uname VARCHAR(25) NOT NULL,
 ucity VARCHAR(25) NOT NULL,
 addr VARCHAR(25) NOT NULL
)
~!@PARTITION BY LIST COLUMNS (ugroupid, ucity)@!~(
PARTITION p0 VALUES IN ((1,'Seattle'),(1,'Seoul'),(2,'Tokyo'),(2,'Sydney')),
PARTITION p1 VALUES IN ((1,'Berlin'), (2,'Seattle'), (1,'Tokyo')),
PARTITION p2 VALUES IN ((1,'Sydney'), (2,'Seoul'), (2,'Berlin'))
); 
```

------------------------------------------------------------------------------
For `LIST` partitioning, the partition key or its expression must be of type `INT`, and it is required that only one column or one column expression can be placed after `PARTITION BY LIST` clause, multi-column are not allowed. TiDB introduces `LIST COLUMNS` partitioning.

`LIST COLUMNS` partitioning is a variant of `LIST` partition. You can use multiple columns as partition keys. Besides the `INT` data type, you can also use the columns using the string (like `CHAR`, `VARCHAR`), `DATE`, and `DATETIME` data types as partition columns. For example:

```
tidb> CREATE TABLE users (
-> uid INT NOT NULL,
-> uname VARCHAR(25) NOT NULL,
-> ucity VARCHAR(50) NOT NULL,
-> addr VARCHAR(25) NOT NULL
-> )
-> PARTITION BY LIST COLUMNS ( ucity )(
-> PARTITION p0 VALUES IN ('Seattle','Seoul','Tokyo','Sydney'),
-> PARTITION p1 VALUES IN ('Berlin', 'Cairo', 'Madrid'),
-> PARTITION p2 VALUES IN ('Vancouver', 'Ottawa', 'Chicago')
-> );
Query OK, 0 rows affected (0.54 sec)

tidb> CREATE TABLE gusers (
-> uid INT NOT NULL,
-> ugroupid INT NOT NULL,
-> uname VARCHAR(25) NOT NULL,
-> ucity VARCHAR(50) NOT NULL,
-> addr VARCHAR(25) NOT NULL
-> )
-> PARTITION BY LIST COLUMNS ( ugroupid, ucity ) (
-> PARTITION p0 VALUES IN ((1,'Seattle'),(1,'Seoul'),(2,'Tokyo'),(2,'Sydney')),
-> PARTITION p1 VALUES IN ((1,'Berlin'), (2,'Seattle'), (1,'Tokyo')),
-> PARTITION p2 VALUES IN ((1,'Sydney'), (2,'Seoul'), (2,'Berlin'))
-> );
Query OK, 0 rows affected (0.55 sec)
```

Please note that the partition columns used by `LIST COLUMNS` do not support expressions, as shown below:

```
tidb> CREATE TABLE users (
-> uid INT NOT NULL,
-> uname VARCHAR(25) NOT NULL,
-> ucity VARCHAR(25) NOT NULL,
-> addr VARCHAR(25) NOT NULL
-> )
-> PARTITION BY LIST COLUMNS ( trim(ucity) )(
-> PARTITION p0 VALUES IN ('Seattle','Seoul','Tokyo','Sydney'),
-> PARTITION p1 VALUES IN ('Berlin', 'Cairo', 'Madrid'),
-> PARTITION p2 VALUES IN ('Vancouver', 'Ottawa', 'Chicago')
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 7 column 33 near "trim(ucity) )(
PARTITION p0 VALUES IN ('Seattle','Seoul','Tokyo','Sydney'),
PARTITION p1 VALUES IN ('Berlin', 'Cairo', ‘Madrid'),
PARTITION p2 VALUES IN ('Vancouver', 'Ottawa', 'Chicago')
)"
```

More details: `https://docs.pingcap.com/tidb/stable/partitioned-table#list-columns-partitioning`.