VALUES

Purpose

You can use this statement to return a set of one or more rows as a table.

The VALUES statement is a table value constructor, which can be used independently or together with UNION, EXCEPT, INTERSECT, and so on.

Syntax

VALUES row_constructor_list [ORDER BY column_designator] [LIMIT number]

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

value_list:
    value[, value][, ...]

column_designator:
    column_index

Parameters

Examples

• Use VALUES to generate a table where rows are sorted by the column_0 column.

  obclient> VALUES ROW(4,6,8), ROW(5,7,9), ROW(1,-2,3) ORDER BY column_0;
  

  The output of the example is as follows:

  +----------+----------+----------+----------+
  | column_0 | column_1 | column_2 |
  +----------+----------+----------+----------+
  | 1 | -2 | 3 |
  | 4 | 6 | 8 |
  | 5 | 7 | 9 |
  +----------+----------+----------+----------+
  3 rows in set
  

• Use VALUES to generate a table where a column contains data of mixed types.

  obclient> VALUES ROW("q", 23, '2022-12-18'), ROW(87, "x+z", 92.6), ROW(25.0009, "Well Smith", '{"a": 11, "b": 55}');
  

  The output of the example is as follows:

  +----------+------------+--------------------+
  | column_0 | column_1   | column_2           |
  +----------+------------+--------------------+
  | q | 23 | 2022-12-18 |
  | 87 | x+z | 92.6 |
  | 25.0009  | Well Smith | {"a": 11, "b": 55} |
  +----------+------------+--------------------+
  

• Use VALUES together with UNION, which is equivalent to SELECT.

  obclient> SELECT 11,22 UNION SELECT 33,44;
  +------+------+
  | 11   | 22   |
  +------+------+
  |   11 |   22 |
  | 33 | 44 |
  +------+------+
  2 rows in set
  
  obclient> VALUES ROW(11,22) UNION VALUES ROW(33,44);
  

  The output of the example is as follows:

  +----------+----------+
  | column_0 | column_1 |
  +----------+----------+
  |       11 |       22 |
  |       33 |       44 |
  +----------+----------+
  2 rows in set