# Other Types

| Data Type | Size     | Size (Not Null) | Max cardinality      | Max values per row |
| --------- | -------- | --------------- | -------------------- | ------------------ |
| ENUM      | 4 bytes  | 2 bytes         | 32,768 unique values | 1                  |
| SET       | 12 bytes | 8 bytes         | 64 unique values     | 64                 |
| BIT       | 9 bytes  | 8 bytes         |                      |                    |

## Using the ENUM Data Type

An `ENUM` is a string value chosen from a user-defined list of values that are specified when creating a table. The values in an `ENUM` list must be quoted strings, which are distinct from others in the list. A column can be defined as `ENUM` using the following syntax:

```sql
CREATE TABLE table_name (  
    ---  
    col_name ENUM('val1','val2',...,'valN')
)
```

The values in an `ENUM` list are indexed, starting from `1`. Consider a column defined as mode `ENUM('Primary','Secondary','Tertiary')`. The following table displays the index for each possible value that the column can have.

| Value       | Index  |
| ----------- | ------ |
| `NULL`      | `NULL` |
| ''          | 0      |
| 'Primary'   | 1      |
| 'Secondary' | 2      |
| 'Tertiary'  | 3      |

The index of a `NULL` value is `NULL`. An empty string denotes an invalid value, and it has an index of `0`. You can use the following query to identify the rows with invalid `ENUM` value assignments:

```sql
SELECT ...
WHERE enum_column = 0;
```

> **❗ Important**: The index of any `ENUM` value denotes its position in the list of values. It has no relation with table indexes.

`ENUM` values cannot be expressions or user-defined variables.  An invalid value is inserted as an empty string. `ENUM` values are sorted based on their index values. The `NULL` values are sorted first, then the empty strings (with index `0`), followed by the values in the `ENUM` list.

The following example shows how `ENUM` values are defined and used.

```sql
CREATE ROWSTORE TABLE delivery_status  
(ID VARCHAR(6),    
  Status ENUM('NOT_DISPATCHED','IN_TRANSIT','DELIVERED'));

INSERT INTO delivery_status VALUES('xdy',1);
INSERT INTO delivery_status VALUES('tsg','IN_TRANSIT');
INSERT INTO delivery_status (ID) VALUES('qfc');

SELECT * FROM delivery_status ORDER BY Status ASC;

```

```output

+------+----------------+
| ID   | Status         |
+------+----------------+
| qfc  | NULL           |
| xdy  | NOT_DISPATCHED |
| tsg  | IN_TRANSIT     |
+------+----------------+
```

This example shows that if an integer values is specified, the `ENUM` value at that index is inserted. Because the `NOT NULL` constraint was not applied on the column, the default value inserted is `NULL`.

Additional `ENUM` values can be added or modified to an existing rowstore table using the `ALTER TABLE` command. To add the additional `ENUM` values "NOT\_SHIPPED" and "RETURNED":

```sql
ALTER TABLE delivery_status MODIFY Status ENUM ('NOT_DISPATCHED','IN_TRANSIT','DELIVERED','NOT_SHIPPED','RETURNED');

```

```output

SHOW CREATE TABLE delivery_status;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                      |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| delivery_status | CREATE ROWSTORE TABLE `delivery_status` (  `ID` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,  `Status` enum('NOT_DISPATCHED','IN_TRANSIT','DELIVERED','NOT_SHIPPED','RETURNED') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL  , SHARD KEY () ) AUTOSTATS_CARDINALITY_MODE=PERIODIC AUTOSTATS_HISTOGRAM_MODE=CREATE SQL_MODE='STRICT_ALL_TABLES' |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

```

Creating an index on an `ENUM` column on columnstore tables is not supported. Altering the `ENUM` values via the `ALTER TABLE` command on columnstore tables is also not supported because the column segments would need to be re-encoded. 

To add `ENUM` values in a columnstore table, there are a couple of workarounds that must be processed offline. See the following examples on how to add the additional `ENUM` values "NOT\_SHIPPED" and "RETURNED" to a columnstore table.

* Create a new table with the updated `ENUM` values, copy the old table to the new table, drop the old table, and rename the new table with the dropped table's name:
  ```sql
  -- Existing columnstore table 
  CREATE TABLE delivery_status  
  (ID VARCHAR(6),    
    Status ENUM('NOT_DISPATCHED','IN_TRANSIT','DELIVERED'));

  -- Create a new table and add values to the ENUM column 
  CREATE TABLE delivery_status_1  
  (ID VARCHAR(6),    
    Status ENUM('NOT_DISPATCHED','IN_TRANSIT','DELIVERED','NOT_SHIPPED','RETURNED'));

  -- Copy the old table to the new table and check to see if the data is correct
  INSERT INTO delivery_status_1(SELECT * FROM delivery_status);
  SELECT * FROM delivery_status_1;

  -- Once you are sure the data from the old table has been copied correctly, drop the old table 
  DROP TABLE delivery_status;

  -- Rename the new table with dropped table's name 
  ALTER TABLE delivery_status_1 RENAME TO delivery_status;
  ```
  If the new `ENUM` values are not correct after copying the data from the old table to the new table, drop the new table (e.g., `delivery_status_1`) and repeat the process from the previous code block.
  ```sql
  DROP TABLE delivery_status_1;
  ```
* Add a new column with the updated `ENUM` values, copy over the values from the old `ENUM` column to the new `ENUM` column, drop the old `ENUM` column, and rename the new `ENUM` column with the dropped `ENUM` column's name:
  ```sql
  -- Existing columnstore table 
  CREATE TABLE delivery_status  
  (ID VARCHAR(6),    
    Status ENUM('NOT_DISPATCHED','IN_TRANSIT','DELIVERED'));

  -- Add a new column with updated ENUM values 
  ALTER TABLE delivery_status ADD COLUMN Status1 ENUM ('NOT_DISPATCHED','IN_TRANSIT','DELIVERED','NOT_SHIPPED','RETURNED');

  -- Copy values from the old column to the new column and check table to see if the data is correct
  UPDATE delivery_status SET Status1 = Status;
  SELECT * FROM delivery_status;

  -- Once you are sure the data from the old column have been copied correctly, drop the old column 
  ALTER TABLE delivery_status DROP COLUMN Status;

  -- Rename the new column with the dropped column's name 
  ALTER TABLE delivery_status CHANGE Status1 Status;
  ```
  If the new `ENUM` values are not correct after copying the data from the old column to the new column, drop the new column (e.g., `Status1`) and repeat the process from the previous code block.
  ```sql
  ALTER TABLE delivery_status DROP COLUMN Status1;
  ```

## Using the SET Data Type

The SET data type restricts the values that can be inserted for a table column, and can have zero or more string values. However, the string value must exist in the list of values specified for the column at the time of table creation.

A SET column value can contain multiple set members, separated by commas. In such a case,

* the SET member values should not individually contain commas.
* the SET member values can be inserted in any order. The results are retrieved in the same order as listed at the time of table creation.
* even if a SET member value is repeated, the results display the value only once.

The following example displays the usage of the SET data type:

Create a table column with SET data type.

```sql
CREATE TABLE setexample (col SET('v1', 'v2', 'v3', 'v4'));

```

Insert column values as “v1,v2”, “v2,v1”, “v1,v4,v4”, and “v3,v4,v3”.

```sql
INSERT INTO setexample (col) VALUES ('v1,v2'), ('v2,v1'), ('v1,v4,v4'), ('v3,v4,v3');

```

The result is retrieved in the same order in which the SET member values were listed during table creation and duplicate values are ignored:

```sql
SELECT col FROM setexample;

```

```output

+------------------------------+
| col                          |
+------------------------------+
| v1,v2                        |
| v1,v2                        |
| v1,v4                        |
| v3,v4                        |
+------------------------------+

```

## Using the BIT Data Type

The `BIT` data type is used to represent bit vectors. It stores bit values in binary notation, i.e., a binary value represented using `0`s and `1`s. A value of type `BIT(n)` can store `n`-bit values, where `n` can range from `1` to `64`. However, SingleStore gives a warning if the size of `BIT` type column is not `64`.

## Bit-Value Literals

The `BIT` data type can also be used to store and represent bit-value literals. By default, a bit-value literal is stored as a binary string. In numeric expressions, a bit-value literal is treated as an integer equivalent of the `BIT` value. A bit-value literal can be specified using any of the following notations:

* `b'101'` or `B'101'` - the leading `b` is case-insensitive
* `0b101` - the leading `0b` is case-sensitive

If you assign a value that is less than `n` bits long, the value is left-padded with `0`s. For example, if you assign the value `b'1100'` to a `BIT(7)` column type, the assigned value is `b'0001100'`.

## Examples

The following example shows that bit-value literals are stored as binary strings.

```sql
SELECT b'1100101' AS 'String Value', CHARSET(b'1100101') AS 'Charset';

```

```output

+--------------+---------+
| String Value | Charset |
+--------------+---------+
| e            | binary  |
+--------------+---------+
```

The following example shows how bit-value literals behave as integers in a numerical expression.

```sql
SELECT b'1100101' + 9;

```

```output

+----------------+
| b'1100101' + 9 |
+----------------+
|            110 |
+----------------+
```

***

Modified at: September 25, 2025

Source: [/db/v9.1/reference/sql-reference/data-types/other-types/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/other-types/)

(An index of the documentation is available at /llms.txt)