Skip to content
Coordinator DB
View as Markdown
View as Markdown

csv_with_names_and_types ​

Description ​

CSV with column names in its first row and runtime type names in its second row. Input checks the type header by default. Runtime output types can be wider than the original column declaration.

Input and output. Select it with FORMAT csv_with_names_and_types. Names ignore ASCII case; use the snake_case name shown here.

Two records precede the values: column names, then type names. The type record is checked against the bound target types by default. Both records use CSV quoting.

Output headers describe runtime values and can therefore show a wider integer type than the source column declaration. When round-tripping, choose compatible target types or explicitly configure the header policy.

text
SELECT expressions FORMAT csv_with_names_and_types;
INSERT INTO table [(columns)] [SETTINGS ...] FORMAT csv_with_names_and_types
<data>

Example usage ​

sql
SELECT CAST(7 AS Int64) AS id, 'Ada' AS name FORMAT csv_with_names_and_types;
text
"id","name"
"Int64","String"
7,"Ada"

Format settings ​

Use a statement SETTINGS clause; these options are not persisted by SET.

Setting Type Default Meaning
csv_check_types Bool 1 Compare the input types header with the bound target column types; when off, skip that header.
csv_crlf Bool 0 End each written row with a carriage return and a line feed instead of a line feed.
csv_double_quotes Bool 1 Read a field that starts with a double quote as quoted text; when off, the quote is an ordinary character.
csv_empty_as_default Bool 1 Give an empty unquoted field the column's DEFAULT; when off, read it as empty text.
csv_enum_by_number Bool 0 Read a whole number in an Enum column as the label's number instead of its name.
csv_match_names Bool 1 Match input names to target columns ignoring ASCII case; when off, skip the names header and read in target order.
csv_null_text String \N The unquoted text that stands for NULL when reading and writing; it must stay one untrimmed field under the current CSV settings.
csv_separator Char , The character between fields when reading and writing: one ASCII character that is not a letter, digit, quote, backslash, or line break. Write a tab as \t.
csv_single_quotes Bool 0 Also read a field that starts with a single quote as quoted text.
csv_skip_lines UInt64 0 Ignore this many lines at the start of the input, before any header row.
csv_trim_blanks Bool 1 Drop spaces and tabs around a field when reading; quotes keep the blanks inside them.
csv_uneven_rows Bool 0 Accept rows with too few or too many fields: missing fields take the column's DEFAULT and extra fields are dropped.
ignore_unknown_fields Bool 0 Skip input keys and header names that match no target column instead of failing.
snappy_mode String basic Read Snappy client files as basic (Hadoop blocks) or framed (standard streaming format).