FORMAT ​

FORMAT chooses the encoding used for a query result. It is available through the native and HTTP server interfaces. The embedded Connection API returns typed values directly.

If no format is specified, the server uses pretty_compact.

Syntax ​

SELECT ... [SETTINGS setting = value, ...] FORMAT format_name
SELECT ... FORMAT format_name [SETTINGS setting = value, ...]

Format names are case-insensitive and use snake_case. For example, use json_each_row; JSONEachRow is not an alias.

SETTINGS in this context applies format options to one statement. It does not persist them as database settings. Each format's reference page lists its available options.

Examples ​

One JSON object per row ​

CREATE TABLE messages (id INTEGER, message TEXT);
INSERT INTO messages VALUES (1, 'hello'), (2, 'goodbye');

SELECT id, message
FROM messages
ORDER BY id
FORMAT json_each_row;

{"id":1,"message":"hello"}
{"id":2,"message":"goodbye"}

A custom CSV separator ​

SELECT 1 AS id, 'hello' AS message
SETTINGS csv_separator = ';'
FORMAT csv;

1;"hello"

Formats can support input, output or both. An input-only format cannot encode a SELECT result. Use system.formats to inspect directions, or browse the format reference.

For INSERT ... FORMAT, place settings before the format name because the following bytes are input data. See INSERT INTO.