# TABLE

It is a table-valued function that converts a PSQL array to a set of rows.

## Syntax

```
TABLE( [DISTINCT] array) [WITH ORDINALITY]

```

## Arguments

* `array`: A PSQL array of scalar values.

## Return Type

A set of table-type rows.

## Remarks

* When used without the `WITH ORDINALITY` option, the `TABLE()` function returns a table with a single column named `table_col`, where each row contains a single element from the input array.
* The `WITH ORDINALITY` option adds an extra column named `table_ord` of type `BIGINT` to the output that contains the 0-based position of each element in the input array. Refer to [Example: Use WITH ORDINALITY Option](https://docs.singlestore.com/#section-id235392027772379.md) and [Example: Use DISTINCT Option](https://docs.singlestore.com/#section-idm234620380352616.md).
* The `TABLE()` function can be used only in the `FROM` clause of a query.
* The array argument to the `TABLE()` function must contain only scalar values of valid column [data types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md).
* Array of `RECORDS` and multi-dimensional arrays are not supported as arguments to the `TABLE()` function.
* PSQL arrays are not supported as a table data type or array literals in SingleStore queries.
* When used as a reference to the second (or subsequent) table in the `FROM` clause (with an argument that is correlated), if the array value passed to `TABLE()` has **N** entries, the output will have **N** \* *number\_of\_values\_from\_the\_table\_on\_the\_left* rows. The values from the table to the left will be repeated for each array entry.
* The `TABLE` function supports `DISTINCT`. `DISTINCT` will de-duplicate the array argument and will generate a table with unique values only.

## Examples

## Example 1

```sql
SELECT * FROM TABLE([1,2,3]);

```

```output

+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+

```

```sql
SELECT * FROM TABLE(["hello", "world"]);

```

```output

+-----------+
| table_col |
+-----------+
| hello     |
| world     |
+-----------+

```

```sql
SELECT * FROM TABLE(JSON_TO_ARRAY('[1,2,3]'));

```

```output

+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+
```

## Example 2

In the `FROM` clause, if the table on the left has **2** rows and the array passed to `TABLE()` has **2** entries, the output will have **4** rows (2\*2). For example,

```sql
CREATE TABLE num (x int);
INSERT INTO num VALUES (1),(2);

```

```sql
SELECT * FROM num, TABLE([1,2]);

```

```output

+------+-----------+
| x    | table_col |
+------+-----------+
|    1 | 1         |
|    1 | 2         |
|    2 | 1         |
|    2 | 2         |
+------+-----------+
```

## Example 3

The following example demonstrates the usage of `TABLE()` function through UDFs.

```sql
CREATE TABLE square
  (num INT PRIMARY KEY);

INSERT INTO square VALUES (1),(2),(3),(4),(5);

DELIMITER //

CREATE OR REPLACE FUNCTION to_array (x INT)
  RETURNS ARRAY(INT) AS
  DECLARE
    arr ARRAY(INT) = CREATE_ARRAY(x+1);
  BEGIN
    FOR i IN 0..x LOOP
      arr[i] = i * i;
    END LOOP;
  RETURN arr;
END //

DELIMITER ;

SELECT num, table_col AS "SQUARE"
FROM square INNER JOIN TABLE(to_array(6))
  ON table_col = num*num ORDER BY num;

```

```output

+-----+--------+
| num | SQUARE |
+-----+--------+
|   1 |      1 |
|   2 |      4 |
|   3 |      9 |
|   4 |     16 |
|   5 |     25 |
+-----+--------+

```

## Example 4

The `TABLE` function can be used to expand a field in a table.

```sql
CREATE TABLE empRole(Name varchar(20), Role JSON);
INSERT INTO empRole VALUES
  ('Adam', '["Team Lead","Security Engineer"]'),
  ('Mary','["Product Manager"]');

SELECT * FROM empRole;

```

```output

+-------+-----------------------------------+
| Name  | Role                              |
+-------+-----------------------------------+
| Adam  | ["Team Lead","Security Engineer"] |
| Mary  | ["Product Manager"]               |
+------+------------------------------------+

```

```sql
SELECT Name, table_col AS "Title" FROM empRole
JOIN TABLE(JSON_TO_ARRAY(Role));

```

```output

+------+---------------------+
| Name | Title               |
+------+---------------------+
| Adam | "Team Lead"         |
| Adam | "Security Engineer" |
| Mary | "Product Manager"   |
+------+---------------------+

```

## Example 5: Use DISTINCT Option

To return only distinct values from the input array, eliminating duplicates, use the `DISTINCT` option as:

```sql
SELECT * FROM TABLE(DISTINCT [1,1,2,3,2]);

```

```output

+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+
```

```sql
SELECT * FROM TABLE(DISTINCT['null', 'primary', 'secondary', 'secondary', 'tertiary', 'null', 'secondary', 'tertiary']) WITH ORDINALITY;

```

```output

+-----------+-----------+
| table_col | table_ord |
+-----------+-----------+
| null      |         0 |
| primary   |         1 |
| secondary |         2 |
| tertiary  |         3 |
+-----------+-----------+
```

## Example 6: Use WITH ORDINALITY Option

The following example shows how to add an ordinality column to the output using the `WITH ORDINALITY` option. Optionally, you can specify aliases for the `table_col` and `table_ord` columns.

```sql
SELECT table_col AS Label, table_ord AS Priority 
FROM TABLE(["alpha", "beta", "gamma", "delta"]) WITH ORDINALITY;

```

```output

+-------+----------+
| Label | Priority |
+-------+----------+
| alpha |        0 |
| beta  |        1 |
| gamma |        2 |
| delta |        3 |
+-------+----------+
```

You can also use the `WITH ORDINALITY` option with the `TABLE()` function in joins. For example, consider the following `tasks` table:

```sql
CREATE TABLE tasks (
    task_id INT PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    label VARCHAR(50) NOT NULL
);

INSERT INTO tasks (task_id, task_name, label) VALUES
(1101, 'Task A', 'gamma'),
(1102, 'Task B', 'alpha'),
(1103, 'Task C', 'beta'),
(1104, 'Task D', 'delta');
```

Use `TABLE() WITH ORDINALITY` as follows to assign a priority to each label and then order the tasks by priority:

```sql
WITH labels AS (
    SELECT table_col AS Label, table_ord AS Priority
    FROM TABLE(["alpha", "beta", "gamma", "delta"]) WITH ORDINALITY
)
SELECT t.task_name AS Task, l.Priority
FROM tasks t JOIN labels l
ON t.label = l.Label
ORDER BY l.Priority;

```

```output

+--------+----------+
| Task   | Priority |
+--------+----------+
| Task B |        0 |
| Task C |        1 |
| Task A |        2 |
| Task D |        3 |
+--------+----------+
```

Alternatively, you can run this query as:

```sql
SELECT t.task_name AS Task, l.table_ord AS Priority 
FROM tasks t JOIN TABLE(["alpha", "beta", "gamma", "delta"]) WITH ORDINALITY AS l 
ON t.label = l.table_col ORDER BY l.table_ord;

```

```output

+--------+----------+
| Task   | Priority |
+--------+----------+
| Task B |        0 |
| Task C |        1 |
| Task A |        2 |
| Task D |        3 |
+--------+----------+
```

***

Modified at: February 11, 2026

Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/table/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/table/)

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