Values

Description

The Values format prints every row in brackets.

• Rows are separated by commas without a comma after the last row.
• The values inside the brackets are also comma-separated.
• Numbers are output in a decimal format without quotes.
• Arrays are output in [].
• Strings, dates, and dates with times are output in quotes.
• Escaping rules and parsing are similar to the TabSeparated format.

During formatting, extra spaces aren’t inserted, but during parsing, they are allowed and skipped (except for spaces inside array values, which are not allowed). NULL is represented as NULL.

The minimum set of characters that you need to escape when passing data in the Values format:

• single quotes
• backslashes

This is the format that is used in INSERT INTO t VALUES ..., but you can also use it for formatting query results.

Example usage

Inserting data

The Values format is what INSERT uses, so any INSERT ... VALUES statement is already using it. The FORMAT Values clause can be stated explicitly, and the rows can be supplied from a stream or a file. Each row is a bracketed, comma-separated tuple, with the tuples themselves separated by commas:

CREATE TABLE t (id UInt32, name String, values Array(UInt32)) ENGINE = Memory;

INSERT INTO t FORMAT Values (1, 'a', [10, 20]), (2, 'b', [30]);

SELECT * FROM t ORDER BY id;

┌─id─┬─name─┬─values──┐
│  1 │ a    │ [10,20] │
│  2 │ b    │ [30]    │
└────┴──────┴─────────┘

Using expressions on input

Unlike most input formats, Values can evaluate SQL expressions in each field rather than only accepting literals. This is controlled by input_format_values_interpret_expressions (enabled by default): when a field cannot be read by the fast streaming parser, ClickHouse falls back to the SQL parser and interprets the field as an expression.

CREATE TABLE prices (item String, total UInt32) ENGINE = Memory;

INSERT INTO prices FORMAT Values ('apple', 3 * 4), ('pear', length('hello') + 10);

SELECT * FROM prices ORDER BY total;

┌─item──┬─total─┐
│ apple │    12 │
│ pear  │    15 │
└───────┴───────┘

Selecting data

The Values format can also be used to format query results. Numbers are written without quotes, arrays in [], and strings and dates in single quotes; single quotes and backslashes inside strings are escaped with a backslash, and NULL is written as NULL:

SELECT 1 AS a, 'O''Reilly' AS b, NULL::Nullable(String) AS c FORMAT Values;

(1,'O\'Reilly',NULL)

Format settings